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

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 ‘%[%][[]%’