Trying hard to find an equivalent for the below SQL. Any help/suggestion

PATINDEX('%[0-9,c][0-9,p,a][][A-Z,0-9][A-Z,0-9][A-Z,0-9][][1-9,A-Z][0-9,A-Z][0-9][0-9][0-9]%', FIELDNAME) > 0

  • Including some documentation on this function and some desired input/output combos would go a long way here. – Adrian Larson Nov 2 '17 at 13:29

There is no equivalent way to search using SOQL. Searching via Regular Expression is not supported. The closest the query language gets is the LIKE operator:

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:

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

Normally the workaround for such functionality would be to implement a Formula Field and query on that, but formula support for Regular Expressions is limited to Validation Rules and Process Builder, no support in an actual formula field.

You're out of luck.

| improve this answer | |

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.