Database Programming: Something Simple That, LIKE, ESCAPEd Me Until Now

There's no rocket science in this post, just a neat little piece of syntax which has been around for awhile and yet had managed to escape my awareness until Ning asked a really interesting question that I thought I knew the answer to:

How do you use LIKE to find strings containing a literal %?

I always used a SUBSTRING query for stuff like this, but I dutifully went off to check Books OnLine (ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMProg3/html/f8718bc5-cbc2-44ef-b47f-0547bd855d92.htm), and there it was, as clear as the nose on my face (my emphasis added):

 match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

It turns out that if you want to reference a literal wildcard expression in a LIKE pattern, this little nugget allows you to do so without resorting to the SUBSTRING syntax. Here's a SELECT that finds all of the stored procedure, UDF, and trigger lines in a database which contain a literal %:

SELECT *

FROM syscomments

WHERE [text] LIKE '%!%%' ESCAPE '!'

This syntax is good for both SQL Server 2000 and SQL Server 2005.

-wp