devxlogo

Dealing with Wildcard Characters in Oracle

Dealing with Wildcard Characters in Oracle

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 problem?the ESCAPE character.

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:

1%4-t1%52-t2%145-t2%31-t

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:

1%4-t1%52-t

See also  5 Ways to Improve Customer Experience
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist