Search for % sign in SQL.

June 25, 2008

This one came up recently so I thought I'd blog it.

MS SQL server uses the percentage sign (%) as a wildcard when you use the like operator. For example:

SELECT 
  post_id
  , post_title
FROM Posts
WHERE post_title LIKE '%100%'

This will return all the posts that have 100 in the post title, regardless of whether it is "Made with 100% ColdFusion" or "Over 100,000 cats can't be wrong".

So what if you want to search for the percentage sign? You need to escape it like this:

SELECT 
  post_id
  , post_title
FROM Posts
WHERE post_title LIKE '%100[%]%'

This will find all posts that contain "100%" in the title of the post.

No comments

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.