?
Solved

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

Posted on 2006-04-21
5
Medium Priority
?
4,342 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:mightyone
5 Comments
 
LVL 6

Author Comment

by:mightyone
ID: 16508441
ahh i found out....

oracle needs to be told to escape \

select name from users where name like '%\_%' ESCAPE '\';
0
 
LVL 17

Expert Comment

by:ram_0218
ID: 16508550
An alternative to that,

select u1 from (select 'user1' u1 from dual)
where instr(u1,'_') >0
0
 
LVL 2

Expert Comment

by:fouaddba
ID: 16511971
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
 
LVL 6

Author Comment

by:mightyone
ID: 16514600
@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
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 16525429
select name from users where instr(name,chr(95)) > 0;
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question