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 '\''.

it is because of that underscore works same as percent in like clause but it refers to just one character. — Hamidreza, Jan 27 '14 at 12:06
I want to search value with underscore without using escape. How to do? — Ramesh, Jan 27 '14 at 12:08
You have the same string "test_123" in your prod environment, and your query is not able to find it? — Incognito, Jan 27 '14 at 12:16
`_` matches any character including `_`, so `test_123` will be found :) — Egor Skriptunoff, Jan 27 '14 at 12:26

Hamidreza · Accepted Answer · 2014-01-27T12:31:56.030

44

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%';

edited Jan 27 '14 at 12:31

answered Jan 27 '14 at 12:26

Hamidreza

3,038
1
18
15

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 – Superdooperhero Jun 13 '17 at 11:37
That `ESCAPE` clause is easy to miss; this answer could do with pointing it out more. – jpmc26 Nov 30 '17 at 20:08
1

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." – osullic Nov 20 '20 at 12:28

score 5 · Answer 2 · answered May 10 '18 at 06:35

5

In Oracle, you can also use ESCAPE like this:

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

answered May 10 '18 at 06:35

Sohail xIN3N

2,951
2
30
29

score 5 · Answer 3 · answered Aug 13 '19 at 23:11

5

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~%';

answered Aug 13 '19 at 23:11

laughsloudly

604
7
12

score 1 · Answer 4 · edited Sep 24 '20 at 12:17

1

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

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

edited Sep 24 '20 at 12:17

Ian Campbell

23,484
14
36
57

answered Sep 24 '20 at 11:35

Laszlo

11
1

score 0 · Answer 5 · answered Jul 26 '21 at 13:53

0

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 ...

answered Jul 26 '21 at 13:53

Çağlar Duman

133
2
6

Underscore is not working in oracle like clause

5 Answers5

Linked