Partager via

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

  • Article

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

  • Anonymous
    January 01, 2003
    I've been doing some performance tuning work over the last couple of days, and I've found a new use for

  • Anonymous
    January 01, 2003
    Fair enough, Denis..  I think both syntaxes are equally viable..

  • Anonymous
    January 01, 2003
    That'll work too..  until you want to find a bracket and a percent sign in the same string.. :)

  • Anonymous
    October 04, 2006
    What abut this?SELECT *FROM syscommentsWHERE [text] LIKE '%[%]%'

  • Anonymous
    October 05, 2006
    Bracket and percent sign goes like thisSELECT *FROM syscommentsWHERE [text] LIKE '%[%][[]%'