Opinionated Software

… we have opinions about everything!

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.


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.