Underscore is not working in oracle like clause

Question

When development, I used 'test_1%' to find 'test_123' in like. But in production environment its not working. Using 'escape '\'' is working. is there any setting needs to set in oracle? I want to use without escape '\''.

Lelia

it is because of that underscore works same as percent in like clause but it refers to just one character. – Garner

I want to search value with underscore without using escape. How to do? – Lelia

You have the same string "test_123" in your prod environment, and your query is not able to find it? – Fluidize

_ matches any character including _, so test_123 will be found :) – Celibacy

Answer 1

try this in SQL Developer:

SELECT * FROM TABLE1 WHERE NAME LIKE 'test\_1%' escape '\'

in sql plus:

set escape '\'
SELECT * FROM TABLE1 WHERE NAME LIKE 'test\_1%';

Garner

How can this be done in a PLSQL package. When I submit the package for compilation the escape '\' gives me a ORA-00933: SQL command not properly ended – Seedy

That ESCAPE clause is easy to miss; this answer could do with pointing it out more. – Christinachristine

I wanted to point out something from the Oracle docs that I think is important to know... "If esc_char is not specified, then there is no default escape character." – Lillielilliputian

Answer 2

In Oracle, you can also use ESCAPE like this:

SELECT * FROM name_of_table WHERE description LIKE 'testing\_%' ESCAPE '\';

Marillin

Answer 3

The other answers using the ESCAPE '\' didn't work for me, but I was able to overcome this issue by using a REPLACE function:

SELECT * FROM name_of_table WHERE REPLACE(description, '_', '~') LIKE 'testing~%';

Jeaniejeanine

Answer 4

For me this has worked (using ^ as escape character):

select * from all_tables where  table_name  not like '%^_%' escape '^' ;

St

Answer 5

Using _ as escape character and using double _ in like condition has worked for me.

If you want to add more condition, put ESCAPE keyword after LIKE condition.

SELECT * FROM USER_TABLES WHERE TABLE_NAME LIKE '%__%' ESCAPE '_' AND ...

Burnight