How do I perform an accent insensitive query in SOQL?

Question

I am trying to do an accent insensitive query in SOQL. Using something like :

SELECT * FROM table_name WHERE column_name LIKE '%andre%'

I would like the database to return me a list of rows which column_name are presented with and without accent. column_name solutions such as Alexandre, Andre or André. I already checked SalesForce link : http://www.salesforce.com/us/developer/docs/soql_sosl/index_Left.htm#CSHID=sforce_api_calls_sosl_where.htm|StartTopic=Content%2Fsforce_api_calls_sosl_where.htm|SkinName=webhelp discussing about the different comparison operators but without success.

Has anyone already been confronted to that type of problem in SOQL, and found an answer to it ?

Answer 1

Quoting @sfdcfox from Accents in SOQL queries (my emphasis):

You'd have to perform a SOSL instead: SOQL doesn't support accent translation, according to any docs I've ever seen. However, SOSL uses a full-text search index that is accent agnostic.

There are further notes in that thread that you should use the unaccented characters to drive the search as this will return accented results. However, if you search for a specific accented character the unaccented results will not appear.

Assuming the underlying text search uses Oracle Text and with some trial and error you may be able to determine which combination of BASE_LETTER, ALTERNATE_SPELLING, and OVERRIDE_BASE_LETTER attributes are being used.

Answer 2

Well, I don't think either SOQL or SOSL can give you the results for the phonetic match. You have to use the phonetic algorithm to match the word. May be you need to make an API call to the service who provides the sound/phonetic matching. There is article on the same should it help you.

https://arungaikwad.wordpress.com/tag/salesforce-and-soundex/

Answer 3

Like operator is now provided in SOQL.

The 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 Bappl: view sourceprint?

SELECT AccountId, FirstName, lastname FROM Contact WHERE lastname LIKE 'appl%'

Please mark it as solved, if it resolved your issue.