Oracle SQL: Escape in LIKE clause

person Jason Huangfolder_openCode, Oracle, SQLlocal_offer, , , , access_time April 23, 2010

The underscore in oracle SQL, when it’s used in LIKE clause represent a one character wild card.
For example:
like ‘V_KAA’ (returns VaKAA, VbKAA)

But you want to find something like V_KAAA (including a under score_), you would need to escape the _ character in like clause.

For example:

select * from table where ID LIKE 'V\_KAAA' escape '\'

(put the escape character in front of character you want to escape, and use escape clause to specify the character that you used for escape.)

The above example escapes with character \. So _ become just underscore, not the one character wild card used in Oracle SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>