Although SQL Server does not support regular expressions natively, character classes in LIKE clauses offer a subset of the flexibility that is often forgotten. Some examples:
For example:
SELECT TOP 10 * FROM PS_JOB WHERE EMPL_STATUS LIKE '[ALPS]' |
Is equivalent to:
SELECT TOP 10 * FROM PS_JOB WHERE EMPL_STATUS IN ('A','L','P','S') |
But this is NOT the same as this because the character class check is normally case sensitive:
SELECT TOP 10 * FROM PS_JOB WHERE EMPL_STATUS LIKE '[aLPS]' |
You can also mix ranges and lists of values. For example, to find all OPRIDs in PSOPRDEFN starting with a lowercase letter or numerics 1,2,6,7 or 8:
SELECT * FROM PSOPRDEFN WHERE OPRID LIKE '[a-z126-8]%' |
And you can negate the character class list using a leading ^ just like regular expressions e.g.
SELECT TOP 10 * FROM PSOPRDEFN WHERE OPRID LIKE '[^0-9A-Z]%' |
will find the rows where the OPRID does not start with a numeric digit 0-9 or an uppercase letter.
And whilst you cannot have the “n-times” modifier of regular expressions using brackets { and }, you can emulate the behaviour using a mixture of techniques. For example, finding OPRIDs starting with 3 numerics followed any 3 characters (remembering that a single underscore in a like matches any character in that position), not having an uppercase B in column 7 and with an uppercase W in column 8:
SELECT TOP 10 * FROM PSOPRDEFN WHERE OPRID LIKE '[0-9][0-9][0-9]___[^B][W]' |