CODEWITHSUNDEEP
sqloracle-database
Benoit
Benoit

Reputation: 79155

Any function escaping a string for literal LIKE search?

I noticed that performance for a filter in Oracle can be very different depending on whether you write

WHERE col LIKE 'abc%'

or

WHERE SUBSTR(col,1,3) = 'abc'

So, I would like to always use LIKE queries. But my prefix can contain any ASCII character, and thus I have to escape %, _, and the escape character itself.

Both ways are painful for the guy that will read it afterwards. Is there an alternative function, in the style of WHERE begins_with(col, pat) > 0, or WHERE col LIKE like_literal(pat) || '%' or do I have to write it myself?

Upvotes: 1

Views: 102

Answers (1)

Akhil Khandelwal
Akhil Khandelwal

Reputation: 103

You can determine your own escape character.

WHERE col LIKE 'A\_B%' ESCAPE '\';

In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

Please read following link https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions016.htm

Upvotes: 1

Related Questions