Use Wildcard Characters as Literal Characters (in LIKE comparisons)

We often use SQL queries with LIKE comparisons. When we do so, we usually use the % wildcard character. For instance, if i want to obtain every book with the word SQL in its title, in a Books database table, I would write something like:

SELECT * FROM Books WHERE Title LIKE '%SQL%'

You can also use the other wildcard characters/expressions: _ (underscore), [] and [^].

The problem comes when you need to find strings that contain these wildcard characters themselves. For instance, imagine you're searching a list of CD's and you want to find all CD's that contain the string [SOUNDTRACK]. As you can see you're searching a string that contains the wildcard characters [ and ].

You want those characters to be considered literal characters instead of wildcard characters, so you have enclose them in brackets, like so:

[[]SOUNDTRACK]

The ] character, for some reason, doesn't need to be enclosed in brackets but all other wildcard characters do: [, _ and %

So if you're going to run a LIKE comparison with a string that can contain wildcard characters and you want them to be considered like literal characters you can use the following trick (i'm using the soundtrack example):

DECLARE @str AS VARCHAR(255)
SET @str = '[SOUNDTRACK]'

SET @str = replace(@str, '[', '[[]')
SET @str = replace(@str, '_', '[_]')
SET @str = replace(@str, '%', '[%]')

SELECT * FROM CDs WHERE NAME LIKE '%' + @str + '%'

You can also use the ESCAPE clause. If you want to know more about LIKE comparisons, check here.