7

Hoping there is a simple answer that I am overlooking.


Currently, we are able to search for the following reserved characters with an escape sequence, eg a backslash character '\', in SOSL: & | ! ( ) { } [ ] ^ " ~ * ? : \ ' + -

For example, the following SOSL query will return an account with the name ***Important Account:

FIND {\*\*\*} RETURNING Account

However, the SOQL query

SELECT Id, Name FROM Account WHERE Name LIKE '\*\*\*%'

returns an unknown error parsing query. Double escaping the non-wildcard asterisks (eg '\\*\\*\\*%') returns zero rows.

Per the SOQL documentation, the only reserved characters are the single quote and the backslash characters, and it is possible to escape the following characters: '"_%\ as well as several types of whitespace, but no asterisks, forward slashes, or parens/brackets.

Is there a way to search for the remaining reserved and special characters in SOQL?


Scenario:

I'm using the following method to sanitize strings for search:

public static String sanitizeString(String searchText) {
    // \\p{Punct} in the pattern class matches all punctuation, including single quotes
    // https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html
    String REGEXP = '\\p{Punct}';
    // \\$0 is the entire matched string, and \\ escapes it
    String REPLACE = '\\\\$0';
    return searchText.replaceAll(REGEXP, REPLACE);
}

The sanitized string is then appended with % wildcards and passed into a dynamic query.

This works for a SOSL query with reserved characters, such as the above example, but the functionality does not appear to exist in SOQL. Due to the limitations of SOSL, the preference is to use a SOQL query.


I have reviewed the following answer and tried the following formats:

Single escape: System.debug(Database.query('Select Id, Name from Account where Name Like \'\\*%\''));

Result: System.QueryException: Invalid string literal '\*%'. Illegal character sequence '\*' in string literal.

Double escape: System.debug(Database.query('Select Id, Name from Account where Name Like \'\\\\*%\''));

Result: Zero rows returned.

I have also tried URL Encoding to no avail.

2
  • 6
    Very well researched and written question. Welcome to SFSE. Please note there is no need to sign or otherwise thank the community in your posts. We are quite terse here. :)
    – Adrian Larson
    Jun 29, 2021 at 16:54
  • 1
    I think your answer is in the SOQL documentation on reserved characters. There are different reserved characters between SOQL and SOSL so there's no need to escape the asterisks here. Executing these queries: SELECT Id, Name FROM Account WHERE Name LIKE '***%' or System.debug(Database.query('Select Id, Name from Account where Name Like \'***%\'')); both return an Account with the Name "***Important Account" for me. I think you're needlessly escaping some of your SOQL characters and I would write some tests around the different characters you're anticipating.
    – nbrown
    Jun 29, 2021 at 17:07

1 Answer 1

6

The * character is not a special character as far as SOQL is concerned. Here's a trivial proof for you:

Example of SOQL LIKE with *

And here's the same in Apex:

System.debug(
    Database.query(
        'SELECT Name FROM Account WHERE Name LIKE \'**Demo\''  
    )
);

Or, adding a wildcard at the end:

System.debug(
    Database.query(
        'SELECT Name FROM Account WHERE Name LIKE \'**Demo%\''  
    )
);

In other words, you're likely over-sanitizing your query.

The code that should work correctly in your org would be:

System.debug(
  Database.query(
    'Select Id, Name from Account where Name Like \'*%\''
  )
);
1
  • I knew it was something silly like this! Thanks for the prompt response! Jun 29, 2021 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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