Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

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

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

  • Comments 5
  • Likes

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
  • What abut this? SELECT * FROM syscomments WHERE [text] LIKE '%[%]%'

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

  • Bracket and percent sign goes like this SELECT * FROM syscomments WHERE [text] LIKE '%[%][[]%'

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

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment