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.