SOQL query to fetch records with reserved characters ('*', '/', '(', ')')

Question

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.

Answer 1

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

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 \'*%\''
  )
);