Developers generally tend to use characters such as %
as a delimiter for storing data in an Oracle database. But because Oracle views the %
character as a Wildcard character, it can't use it as a delimiter effectively. Fortunately, Oracle provides a way out for such a problemthe ESCAPE
The ESCAPE keyword comes handy when you have a character in your data which is same as a Wildcard character.
Assume that a field called ObjectID in a table ObjectTable has a value:
Now, suppose you want to retrieve all the values starting with 1%
followed by any other characters. Your query will look like this:
SELECT ObjectID FROM ObjectTable WHERE ObjectID LIKE '1\%%' ESCAPE '\'
Here the keyword ESCAPE
and the character in the quotes (\
) specifies that there is a escape character and the character is specified in the quotes.
This allows you to retrieve the following from the above query: