Categories
Hints and Tips SQL

Underscore matching in LIKE clauses

Speaking with an old colleague last week made me realize that some developers do not appreciate the significance of the underscore (_) in LIKE clauses.

A somewhat artificial example from PeopleSoft:

A developer wants to find all record definitions where the override SQL table name starts with ‘PS_’ followed by the string ‘DEFN’. They try this:

SELECT * FROM PSRECDEFN WHERE SQLTABLENAME LIKE 'PS_%DEFN%'

But that gives them too many rows because the underscore matches any character in that position. So they craft a better solution:

SELECT * FROM PSRECDEFN WHERE SUBSTRING(SQLTABLENAME,1,3) = 'PS_' AND SQLTABLENAME LIKE '%DEFN%'

But there is another way using a character class to exactly match the underscore – just wrap the value in [ and ]:

SELECT * FROM PSRECDEFN WHERE SQLTABLENAME LIKE 'PS[_]%DEFN%'

Note: I know there are dozens of alternative approaches and solutions to this problem – the main point of this example is to demonstrate the use of character classes and how useful they can be in certain cases.

Leave a Reply

Your email address will not be published.

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