Opinionated Software

… we have opinions about everything!

Character Classes in SQL Server LIKE —

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]'

Categorised as: Hints and Tips | SQL



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.