select name from users where name like '%\_%';

i am totally stunned.

i know that "_" stands for exact one char so that if i want look for "_" i have to escape it.

but why don't i get the answer i expect?

i have user like

user_1
user_2
user_3

this is what i expect to get from:
select name from users where name like '%\_%';

but i get empty result on oracle 9ir2 patchlevel 7

please help
LVL 6
mightyoneAsked:
Who is Participating?
 
awking00Commented:
select name from users where instr(name,chr(95)) > 0;
0
 
mightyoneAuthor Commented:
ahh i found out....

oracle needs to be told to escape \

select name from users where name like '%\_%' ESCAPE '\';
0
 
ram_0218Commented:
An alternative to that,

select u1 from (select 'user1' u1 from dual)
where instr(u1,'_') >0
0
 
fouaddbaCommented:
if I didn't understand wrong,

that ur trying to search user_
and then only 1 character after that at end, correct

if this is the case then u can try:

where .... like 'user\__'
or
where .... like 'user__'

try & update, if this works.

cheers ;-)
0
 
mightyoneAuthor Commented:
@ram_0218

could you explain what your query does?
specially what does (select 'user1' u1 from dual)
exactly do. it writes sth in variable u1 but what?

@fouaddba

no, I use ora9ir2 and looked for names with underscores but got unexpected results even though i escaped the _ with \
in fact the query i use is far morcomplicated but the prob could be taken down to this point.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.