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.

UPDATED * My Apex code

string accountName= record.Name;
    if(accountName.contains('-'))
    {
        accountName = accountName.replace('-', '%');
    }
    if(accountName.contains(' '))
    {
        accountName = accountName.replace(' ', '%');
    }
    accountName= '%' + accountName+ '%';

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

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];

PS

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.

  • I added the apex code im using to my op – Alexander Atkinsoon Nov 9 at 9:54
  • Hello, i've added some logic to my apex code. I have it check if contains and replace with %. It work for spaces, but no hyphens however... I added it to my OP – Alexander Atkinsoon Nov 9 at 10:57
  • @AlexanderAtkinsoon The method returns a new string with the replaced characters (it does not modify the string) so you need something more like accountName = '%' + accountName.replace('-', '%').replace(' ', '%') + '%';. – Keith C Nov 9 at 11:02
  • Thanks so much! – Alexander Atkinsoon Nov 9 at 11:06

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?

  • 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 Nov 9 at 9:58
  • I guess in short my question comes down to, is it possible to make queries typo-proof? – Alexander Atkinsoon Nov 9 at 10:14
  • now this question is fully understandable and as for me @KeithC gave a nice answer – Oleksandr Berehovskiy Nov 9 at 10:16

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)

  • What if the name is a variable and not hardcoded? – Alexander Atkinsoon Nov 9 at 9:32
  • 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 Nov 9 at 9:39
  • not the array only works with bind variables – Ralph Callaway Nov 9 at 9:39
  • and of course there is dynamic soql that let's you do it all dynamically – Ralph Callaway Nov 9 at 9:40

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.