SOQL Like Query?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

I am trying to make a query that will search for accounts with a name similar or exactly matching a field value.

So for example I have "Sales-Force" in the field. There is an account called "Salesforce" or "Sales Force"

My current query

Select Id, Name FROM Account WHERE Name LIKE '%Sales-Force%'

This isn't returning my accounts named "Sales Force" or "Salesforce" in my query editor.

My actual Apex code

string accountName= record.Name;
accountName= '%' + accountName + '%';

List<Account> accountLookup = new List<Account>();
accountLookup = [Select Id, Name FROM Account WHERE Name LIKE :accountName];

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

asked 1 hour ago

Alexander Atkinsoon

304

add a comment |

up vote
1
down vote

favorite

I am trying to make a query that will search for accounts with a name similar or exactly matching a field value.

So for example I have "Sales-Force" in the field. There is an account called "Salesforce" or "Sales Force"

My current query

Select Id, Name FROM Account WHERE Name LIKE '%Sales-Force%'

This isn't returning my accounts named "Sales Force" or "Salesforce" in my query editor.

My actual Apex code

string accountName= record.Name;
accountName= '%' + accountName + '%';

List<Account> accountLookup = new List<Account>();
accountLookup = [Select Id, Name FROM Account WHERE Name LIKE :accountName];

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

asked 1 hour ago

Alexander Atkinsoon

304

add a comment |

up vote
1
down vote

favorite

I am trying to make a query that will search for accounts with a name similar or exactly matching a field value.

So for example I have "Sales-Force" in the field. There is an account called "Salesforce" or "Sales Force"

My current query

Select Id, Name FROM Account WHERE Name LIKE '%Sales-Force%'

This isn't returning my accounts named "Sales Force" or "Salesforce" in my query editor.

My actual Apex code

string accountName= record.Name;
accountName= '%' + accountName + '%';

List<Account> accountLookup = new List<Account>();
accountLookup = [Select Id, Name FROM Account WHERE Name LIKE :accountName];

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

asked 1 hour ago

Alexander Atkinsoon

304

I am trying to make a query that will search for accounts with a name similar or exactly matching a field value.

So for example I have "Sales-Force" in the field. There is an account called "Salesforce" or "Sales Force"

My current query

Select Id, Name FROM Account WHERE Name LIKE '%Sales-Force%'

This isn't returning my accounts named "Sales Force" or "Salesforce" in my query editor.

My actual Apex code

string accountName= record.Name;
accountName= '%' + accountName + '%';

List<Account> accountLookup = new List<Account>();
accountLookup = [Select Id, Name FROM Account WHERE Name LIKE :accountName];

apex soql query sosl

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

asked 1 hour ago

Alexander Atkinsoon

304

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

asked 1 hour ago

Alexander Atkinsoon

304

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

edited 19 mins ago

Oleksandr Berehovskiy

8,63021935

asked 1 hour ago

Alexander Atkinsoon

304

asked 1 hour ago

Alexander Atkinsoon

304

asked 1 hour ago

Alexander Atkinsoon

304

add a comment |

3 Answers
3

active

oldest

votes

up vote
3
down vote

You can't do it. It is not possible to use value of a field in condition expression, as a value.

According to documentation

fieldExpression uses the following syntax:

fieldName comparisonOperator value

A value used to compare with the value in fieldName. You must supply a
value whose data type matches the field type of the specified field.
You must supply a native value—other field names or calculations are
not permitted. If quotes are required (for example, they are not for
dates and numbers), use single quotes. Double quotes result in an
error.

update

looks like it is XY problem, and I am sure desired result could be achieved by means of formula fields and other tools. Maybe you can post source of this question?

edited 29 mins ago

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

My question comes from a trigger i've created that checks a text field and searches a record based on its value. A lookup field is filled with the ID of the record found. I've been asked to make sure it checks for variations. Incase someone puts a hyphen in the name instead of a space for example it will still find the record.
– Alexander Atkinsoon
21 mins ago

I guess in short my question comes down to, is it possible to make queries typo-proof?
– Alexander Atkinsoon
5 mins ago

now this question is fully understandable and as for me @KeithC gave a nice answer
– Oleksandr Berehovskiy
3 mins ago

add a comment |

up vote
2
down vote

First off, here's the LIKE docs

LIKE Like Expression is true if the value in the specified fieldName
matches the characters of the text string in the specified value. The
LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL;
it provides a mechanism for matching partial text strings and includes
support for wildcards.
The % and _ wildcards are supported for the LIKE operator.
The % wildcard matches zero or more characters.
The _ wildcard matches exactly one character.
The text string in the specified value must be enclosed in single quotes.
The LIKE operator is supported for string fields only.
The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
Don’t use the backslash character in a search except to escape a special character.
For example, the following query matches Appleton, Apple, and Appl,
but not Bap

So for your query Select Id, Name FROM Account WHERE Name LIKE '%Sales-Force%', what you're asking for is all accounts with a name that contains 'Salesforce-Force', so that wouldn't match either accounts.

So to capture all of your examples you'd need this query
SELECT Id, Name FROM Account WHERE Name LIKE 'Sales_Force' OR Name LIKE 'SalesForce' which would match 'Sales-Force', 'Sales Force', 'SalesForce', or 'sAlEsFoRcE' (like is case-insensitive)

answered 50 mins ago

Ralph Callaway

16.3k1172151

What if the name is a variable and not hardcoded?
– Alexander Atkinsoon
47 mins ago

works just fine, you can actually pass in a array of stuff ie. LIKE :values where values is array with ('val1%', '%val1','other%vals')
– Ralph Callaway
40 mins ago

not the array only works with bind variables
– Ralph Callaway
40 mins ago

and of course there is dynamic soql that let's you do it all dynamically
– Ralph Callaway
39 mins ago

add a comment |

up vote
2
down vote

Have you considered:

Select Id, Name FROM Account WHERE Name LIKE '%Sales%Force%'

or:

String likeValue = '%Sales%Force%';

Account accounts = [Select Id, Name FROM Account WHERE Name LIKE :likeValue];

Just noticed Oleksandr had posted this and deleted it: please clarify your question.

PPS

You could add some code that tries to generalize the pattern e.g. replaces non-alphabetic characters with a % and puts a % between each character. Or you could consider e.g. a Metaphone approach to the matching.

edited 7 mins ago

answered 38 mins ago

Keith C

92.7k1087197

I added the apex code im using to my op
– Alexander Atkinsoon
24 mins ago

add a comment |

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "459"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f238840%2fsoql-like-query%23new-answer', 'question_page');

);

Post as a guest

Name

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
3
down vote

You can't do it. It is not possible to use value of a field in condition expression, as a value.

According to documentation

fieldExpression uses the following syntax:

fieldName comparisonOperator value

A value used to compare with the value in fieldName. You must supply a
value whose data type matches the field type of the specified field.
You must supply a native value—other field names or calculations are
not permitted. If quotes are required (for example, they are not for
dates and numbers), use single quotes. Double quotes result in an
error.

update

looks like it is XY problem, and I am sure desired result could be achieved by means of formula fields and other tools. Maybe you can post source of this question?

edited 29 mins ago

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

My question comes from a trigger i've created that checks a text field and searches a record based on its value. A lookup field is filled with the ID of the record found. I've been asked to make sure it checks for variations. Incase someone puts a hyphen in the name instead of a space for example it will still find the record.
– Alexander Atkinsoon
21 mins ago

I guess in short my question comes down to, is it possible to make queries typo-proof?
– Alexander Atkinsoon
5 mins ago

now this question is fully understandable and as for me @KeithC gave a nice answer
– Oleksandr Berehovskiy
3 mins ago

add a comment |

up vote
3
down vote

You can't do it. It is not possible to use value of a field in condition expression, as a value.

According to documentation

fieldExpression uses the following syntax:

fieldName comparisonOperator value

A value used to compare with the value in fieldName. You must supply a
value whose data type matches the field type of the specified field.
You must supply a native value—other field names or calculations are
not permitted. If quotes are required (for example, they are not for
dates and numbers), use single quotes. Double quotes result in an
error.

update

looks like it is XY problem, and I am sure desired result could be achieved by means of formula fields and other tools. Maybe you can post source of this question?

edited 29 mins ago

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

My question comes from a trigger i've created that checks a text field and searches a record based on its value. A lookup field is filled with the ID of the record found. I've been asked to make sure it checks for variations. Incase someone puts a hyphen in the name instead of a space for example it will still find the record.
– Alexander Atkinsoon
21 mins ago

I guess in short my question comes down to, is it possible to make queries typo-proof?
– Alexander Atkinsoon
5 mins ago

now this question is fully understandable and as for me @KeithC gave a nice answer
– Oleksandr Berehovskiy
3 mins ago

add a comment |

up vote
3
down vote

You can't do it. It is not possible to use value of a field in condition expression, as a value.

According to documentation

fieldExpression uses the following syntax:

fieldName comparisonOperator value

A value used to compare with the value in fieldName. You must supply a
value whose data type matches the field type of the specified field.
You must supply a native value—other field names or calculations are
not permitted. If quotes are required (for example, they are not for
dates and numbers), use single quotes. Double quotes result in an
error.

update

looks like it is XY problem, and I am sure desired result could be achieved by means of formula fields and other tools. Maybe you can post source of this question?

edited 29 mins ago

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

You can't do it. It is not possible to use value of a field in condition expression, as a value.

According to documentation

fieldExpression uses the following syntax:

fieldName comparisonOperator value

A value used to compare with the value in fieldName. You must supply a
value whose data type matches the field type of the specified field.
You must supply a native value—other field names or calculations are
not permitted. If quotes are required (for example, they are not for
dates and numbers), use single quotes. Double quotes result in an
error.

update

looks like it is XY problem, and I am sure desired result could be achieved by means of formula fields and other tools. Maybe you can post source of this question?

edited 29 mins ago

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

edited 29 mins ago

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

answered 41 mins ago

Oleksandr Berehovskiy

8,63021935

My question comes from a trigger i've created that checks a text field and searches a record based on its value. A lookup field is filled with the ID of the record found. I've been asked to make sure it checks for variations. Incase someone puts a hyphen in the name instead of a space for example it will still find the record.
– Alexander Atkinsoon
21 mins ago

I guess in short my question comes down to, is it possible to make queries typo-proof?
– Alexander Atkinsoon
5 mins ago

now this question is fully understandable and as for me @KeithC gave a nice answer
– Oleksandr Berehovskiy
3 mins ago

add a comment |

My question comes from a trigger i've created that checks a text field and searches a record based on its value. A lookup field is filled with the ID of the record found. I've been asked to make sure it checks for variations. Incase someone puts a hyphen in the name instead of a space for example it will still find the record.
– Alexander Atkinsoon
21 mins ago

I guess in short my question comes down to, is it possible to make queries typo-proof?
– Alexander Atkinsoon
5 mins ago

now this question is fully understandable and as for me @KeithC gave a nice answer
– Oleksandr Berehovskiy
3 mins ago

My question comes from a trigger i've created that checks a text field and searches a record based on its value. A lookup field is filled with the ID of the record found. I've been asked to make sure it checks for variations. Incase someone puts a hyphen in the name instead of a space for example it will still find the record.
– Alexander Atkinsoon
21 mins ago

I guess in short my question comes down to, is it possible to make queries typo-proof?
– Alexander Atkinsoon
5 mins ago

now this question is fully understandable and as for me @KeithC gave a nice answer
– Oleksandr Berehovskiy
3 mins ago

add a comment |

up vote
2
down vote

First off, here's the LIKE docs

LIKE Like Expression is true if the value in the specified fieldName
matches the characters of the text string in the specified value. The
LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL;
it provides a mechanism for matching partial text strings and includes
support for wildcards.
The % and _ wildcards are supported for the LIKE operator.
The % wildcard matches zero or more characters.
The _ wildcard matches exactly one character.
The text string in the specified value must be enclosed in single quotes.
The LIKE operator is supported for string fields only.
The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
Don’t use the backslash character in a search except to escape a special character.
For example, the following query matches Appleton, Apple, and Appl,
but not Bap

answered 50 mins ago

Ralph Callaway

16.3k1172151

What if the name is a variable and not hardcoded?
– Alexander Atkinsoon
47 mins ago

works just fine, you can actually pass in a array of stuff ie. LIKE :values where values is array with ('val1%', '%val1','other%vals')
– Ralph Callaway
40 mins ago

not the array only works with bind variables
– Ralph Callaway
40 mins ago

and of course there is dynamic soql that let's you do it all dynamically
– Ralph Callaway
39 mins ago

add a comment |

up vote
2
down vote

First off, here's the LIKE docs

LIKE Like Expression is true if the value in the specified fieldName
matches the characters of the text string in the specified value. The
LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL;
it provides a mechanism for matching partial text strings and includes
support for wildcards.
The % and _ wildcards are supported for the LIKE operator.
The % wildcard matches zero or more characters.
The _ wildcard matches exactly one character.
The text string in the specified value must be enclosed in single quotes.
The LIKE operator is supported for string fields only.
The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
Don’t use the backslash character in a search except to escape a special character.
For example, the following query matches Appleton, Apple, and Appl,
but not Bap

answered 50 mins ago

Ralph Callaway

16.3k1172151

What if the name is a variable and not hardcoded?
– Alexander Atkinsoon
47 mins ago

works just fine, you can actually pass in a array of stuff ie. LIKE :values where values is array with ('val1%', '%val1','other%vals')
– Ralph Callaway
40 mins ago

not the array only works with bind variables
– Ralph Callaway
40 mins ago

and of course there is dynamic soql that let's you do it all dynamically
– Ralph Callaway
39 mins ago

add a comment |

up vote
2
down vote

First off, here's the LIKE docs

LIKE Like Expression is true if the value in the specified fieldName
matches the characters of the text string in the specified value. The
LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL;
it provides a mechanism for matching partial text strings and includes
support for wildcards.
The % and _ wildcards are supported for the LIKE operator.
The % wildcard matches zero or more characters.
The _ wildcard matches exactly one character.
The text string in the specified value must be enclosed in single quotes.
The LIKE operator is supported for string fields only.
The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
Don’t use the backslash character in a search except to escape a special character.
For example, the following query matches Appleton, Apple, and Appl,
but not Bap

answered 50 mins ago

Ralph Callaway

16.3k1172151

First off, here's the LIKE docs

LIKE Like Expression is true if the value in the specified fieldName
matches the characters of the text string in the specified value. The
LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL;
it provides a mechanism for matching partial text strings and includes
support for wildcards.
The % and _ wildcards are supported for the LIKE operator.
The % wildcard matches zero or more characters.
The _ wildcard matches exactly one character.
The text string in the specified value must be enclosed in single quotes.
The LIKE operator is supported for string fields only.
The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
Don’t use the backslash character in a search except to escape a special character.
For example, the following query matches Appleton, Apple, and Appl,
but not Bap

answered 50 mins ago

Ralph Callaway

16.3k1172151

answered 50 mins ago

Ralph Callaway

16.3k1172151

answered 50 mins ago

Ralph Callaway

16.3k1172151

answered 50 mins ago

Ralph Callaway

16.3k1172151

What if the name is a variable and not hardcoded?
– Alexander Atkinsoon
47 mins ago

works just fine, you can actually pass in a array of stuff ie. LIKE :values where values is array with ('val1%', '%val1','other%vals')
– Ralph Callaway
40 mins ago

not the array only works with bind variables
– Ralph Callaway
40 mins ago

and of course there is dynamic soql that let's you do it all dynamically
– Ralph Callaway
39 mins ago

add a comment |

What if the name is a variable and not hardcoded?
– Alexander Atkinsoon
47 mins ago

works just fine, you can actually pass in a array of stuff ie. LIKE :values where values is array with ('val1%', '%val1','other%vals')
– Ralph Callaway
40 mins ago

not the array only works with bind variables
– Ralph Callaway
40 mins ago

and of course there is dynamic soql that let's you do it all dynamically
– Ralph Callaway
39 mins ago

What if the name is a variable and not hardcoded?
– Alexander Atkinsoon
47 mins ago

works just fine, you can actually pass in a array of stuff ie. LIKE :values where values is array with ('val1%', '%val1','other%vals')
– Ralph Callaway
40 mins ago

not the array only works with bind variables
– Ralph Callaway
40 mins ago

and of course there is dynamic soql that let's you do it all dynamically
– Ralph Callaway
39 mins ago

add a comment |

up vote
2
down vote

Have you considered:

Select Id, Name FROM Account WHERE Name LIKE '%Sales%Force%'

or:

String likeValue = '%Sales%Force%';

Account accounts = [Select Id, Name FROM Account WHERE Name LIKE :likeValue];

Just noticed Oleksandr had posted this and deleted it: please clarify your question.

PPS

edited 7 mins ago

answered 38 mins ago

Keith C

92.7k1087197

I added the apex code im using to my op
– Alexander Atkinsoon
24 mins ago

add a comment |

up vote
2
down vote

Have you considered:

Select Id, Name FROM Account WHERE Name LIKE '%Sales%Force%'

or:

String likeValue = '%Sales%Force%';

Account accounts = [Select Id, Name FROM Account WHERE Name LIKE :likeValue];

Just noticed Oleksandr had posted this and deleted it: please clarify your question.

PPS

edited 7 mins ago

answered 38 mins ago

Keith C

92.7k1087197

I added the apex code im using to my op
– Alexander Atkinsoon
24 mins ago

add a comment |

up vote
2
down vote

Have you considered:

Select Id, Name FROM Account WHERE Name LIKE '%Sales%Force%'

or:

String likeValue = '%Sales%Force%';

Account accounts = [Select Id, Name FROM Account WHERE Name LIKE :likeValue];

Just noticed Oleksandr had posted this and deleted it: please clarify your question.

PPS

edited 7 mins ago

answered 38 mins ago

Keith C

92.7k1087197

Have you considered:

Select Id, Name FROM Account WHERE Name LIKE '%Sales%Force%'

or:

String likeValue = '%Sales%Force%';

Account accounts = [Select Id, Name FROM Account WHERE Name LIKE :likeValue];

Just noticed Oleksandr had posted this and deleted it: please clarify your question.

PPS

edited 7 mins ago

answered 38 mins ago

Keith C

92.7k1087197

edited 7 mins ago

answered 38 mins ago

Keith C

92.7k1087197

answered 38 mins ago

Keith C

92.7k1087197

answered 38 mins ago

Keith C

92.7k1087197

I added the apex code im using to my op
– Alexander Atkinsoon
24 mins ago

add a comment |

I added the apex code im using to my op
– Alexander Atkinsoon
24 mins ago

I added the apex code im using to my op
– Alexander Atkinsoon
24 mins ago

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky

SOQL Like Query?

3 Answers
3

Your Answer

Post as a guest

3 Answers
3

3 Answers
3

Post as a guest

Comments

Post a Comment

Popular posts from this blog

What does second last employer means? [closed]

List of Gilmore Girls characters

Confectionery

Category

Random preview

SOQL Like Query?

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Post as a guest

3 Answers 3

3 Answers 3

Sign up or log in

Post as a guest

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Comments

Post a Comment

Popular posts from this blog

What does second last employer means? [closed]

List of Gilmore Girls characters

Confectionery

3 Answers
3

3 Answers
3

3 Answers
3