10

I am trying to select data containing four percentage signs in a row. How can I escape the percentage signs so my LIKE condition works?

Thanks

nearly_lunchtime
  • 12,203
  • 15
  • 37
  • 42
  • Can I suggest changing question to "Escape percentage or underscore sign in DB2 SQL" since this also works for the underscore? That'll help people find this question/answer. – KC Baltz Apr 30 '15 at 22:37

1 Answers1

18

Use @% with the escape character clause:

select *
from tbl
where fld like '%@%%' escape '@'

This will search for all records that contain the "%" character in the fld column.

DB2/z has a slightly different format:

select *
from tbl
where fld like {escape '@'} '%@%%'

Obviously, you'll need to choose your escape character carefully so it won't interfere with the rest of your string but this is relatively easy for static strings. Dynamically built strings will require dynamically built queries so that it doesn't use a character from the string.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 1
    You can choose any escape character as long as you double it up any time you use it literally in the LIKE string. LIKE '...' ESCAPE '...' is standard in ANSI SQL92. – bobince Mar 31 '09 at 14:13