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 %:


FROM    syscomments

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

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


Comments (5)

  1. Anonymous says:

    I’ve been doing some performance tuning work over the last couple of days, and I’ve found a new use for

  2. Anonymous says:

    Fair enough, Denis..  I think both syntaxes are equally viable..

  3. Anonymous says:

    That’ll work too..  until you want to find a bracket and a percent sign in the same string.. 🙂

  4. Denis The SQL Menace says:

    What abut this?

    SELECT *

    FROM syscomments

    WHERE [text] LIKE ‘%[%]%’

  5. Denis The SQL Menace says:

    Bracket and percent sign goes like this

    SELECT *

    FROM syscomments

    WHERE [text] LIKE ‘%[%][[]%’

Skip to main content