Using LIKE with special Characters (like the percent sign)
CF Muse Reader Asks:
How do you select a record containing a "%" using the LIKE keyword in sql
Ah... I love the easy ones. If you have a character column that contains the phrase "50% over gross" and "5% over gross" how could you construct a "LIKE" clause that would capture JUST the "5%" over gross if all you had to go on was "5%"? You see? If you did the following...
FROM grossup
WHERE totalDesc LIKE '5%'
FROM grossup
WHERE totalDesc LIKE '5[%]'
Another Related tip - the underscore
Here's another tip. You can use the underscore like a wildcard character. If you had 500, 501 and 5000 as characters in a column and you wanted to get all the columns that were in the hundreds and began with 50 you could not do "LIKE '50%' - because it would pick up the 5000 as well. But you could use the underscore as in this example:
FROM Categories
WHERE cat_num LIKE '50_'
Regular expression pattern syntax
Finally, for those of you who are regex junkies out there you can use pattern matching type syntax as follows...
FROM Policies
WHERE description LIKE 'Policy [A-C]%'
8 Comments
-
nice..very helpful
don't forget the combination technique:
totalDesc LIKE '%5[%]%'
will give you all items that contain 5% - not just begin with 5%... -
Todd - right! nice tip, thanks.
bgoose - yes, sorry It was not clear. A-C would return B as well. If you wanted JUST A and C you could skip the DAsh and use [AC]. If you wanted A,C and E it would be [ACE] and so on. -
Todd, Be very careful doing searches with the wildcard at the beginning ex: totalDesc LIKE '%5[%]%' when you put the wildcard at the beginning of the search term, every record is an initial match and then they will begin to filter out.
The overhead of this type of search can be huge, and will cripple your database until completed. -
Easy one eh? It wasn't my question, but the way I read it, you didn't answer it at all, although you provided some good refreshers on LIKE.
"How do you select a record containing a "%" using the LIKE keyword in sql"
Sounds to me like they are asking how to escape the % character in doing a LIKE search on a string containing a %. I'm still curious about the answer actually. -
doh, nevermind... my reading comprehension failed without my coffee :-o must...find...delete...function. Thanks for the answer :-)
-
Gus:
Any other suggestions then? What if I truly needed to find that - hypothetically speaking - my db's are so perfectly normalized that I hardly EVER use like in my queries ;) -
This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post. http://www.city32.com/87/posts/2_Media/2_Newspaper...
Sites to Watch
Aggregators
RSS
