Skip to main content

Wildcard Characters in T-SQL


The underscore character, when used with the LIKE operator, matches any single character.
When using wildcards as literals, you need to square bracket the character, like '%[_]%[_]%[_]%'

To help me limit the noise with the huge dataset, I employed a simple pattern match to bucket out returned rows.  I was more interested in the classifications and the broad relationships between the rows.

I used something like CASE WHEN [Offering] LIKE '%_%_%_%' THEN 'XXX999_209999_X_X'

My expectation was a field like 'AAA999_201600_A_B' would appear as 'XXX999_209999_X_X', and it did.

But, it also changed 3 digit character entries like 'All' to 'XXX999_209999_X_X'.

Turns out that _ is a single character wildcard, so '%_%_%_%' matched the word ‘All’.  Three 3 mandatory characters surrounded by any optional characters

% is a zero or more character wildcard.

To get the query to work as I intended, I needed to escape the underscore square brackets,  
 '%[_]%[_]%[_]%'

Comments