ColdFusion Muse

Using LIKE with special Characters (like the percent sign)

Mark Kruger December 27, 2005 6:29 PM MS SQL Server Comments (8)

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%'
... it would naturally trap both the 50 and the 5 - because they both "begin" with 5. How do I get the database server to treat the percent sign as a character instead of a wildcard? Using the handy dandy bracket identifiers of course. This code:
FROM    grossup
WHERE   totalDesc LIKE '5[%]'
... would do the trick and return just the "5%" description.

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_'
...this would give you 500 and 501 - because they are both 3 characters beginning with 50. 5000 would be excluded.

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]%'
... which would give you "Policy A" and "Policy C" but not "Policy H". It's a neat trick when you need it. Personally I end up tearing out my hair trying to write regexes. I usually end up writing the CF-Talk list where Michael Dinowitz is the master of the regular expression and seems to pull what I need out of thin air in most cases (ha).

  • Share:


  • todd's Gravatar
    Posted By
    todd | 12/27/05 5:27 PM
    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%...
  • bgoose's Gravatar
    Posted By
    bgoose | 12/27/05 5:52 PM
    Does [A-C] return Policy B too?
  • mkruger's Gravatar
    Posted By
    mkruger | 12/28/05 8:04 AM
    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.
  • Gus's Gravatar
    Posted By
    Gus | 12/29/05 7:08 AM
    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.
  • Doug's Gravatar
    Posted By
    Doug | 12/29/05 8:13 AM
    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.
  • Doug's Gravatar
    Posted By
    Doug | 12/29/05 8:15 AM
    doh, nevermind... my reading comprehension failed without my coffee :-o must...find...delete...function. Thanks for the answer :-)
  • todd's Gravatar
    Posted By
    todd | 12/29/05 8:22 AM

    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 ;)
  • sara's Gravatar
    Posted By
    sara | 8/12/11 1:25 PM
    This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post.