Link to home
Start Free TrialLog in
Avatar of gram77
gram77Flag for India

asked on

Finding strings with special characters in oracle

I want to find strings that contain double ampersands %% and double underscores __ and amp and underscore together

create table dummy
(name varchar2(100));

insert into dummy values ('abc_def_ghi_klo');
insert into dummy values ('abc%def%_klo');
insert into dummy values ('abc%def%__klo');
insert into dummy values ('abc%def%_%%klo');
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

By 'double' do you mean side by side or just that it has two or more somewhere?
select * from dummy where regexp_like(name,'[%_]{2}')
If side by side try:
select name from dummy where instr(name,'%%') > 0 or instr(name,'__') > 0;
you said "ampersand" above but you showed percent '%'  if you meant ampersand then

select * from dummy where regexp_like(name,'[&_]{2}')
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
extending on awking00's idea   missed the "_%" possibility


select name from dummy
where regexp_like(name,'%%|__|%_|_%');

that's why I suggested the  "[%_]{2}"  pattern above,
 it will cover all permutations for me.  but just 2, it's not too difficult to just list them all

sdstuber,
I thought about it but wasn't sure that the "_%" possibility was being considered since the asker indicated "and amp [sic] and underscore together."
fair enough  :)

and if it is the case that _% is not legal, then my first (and followup) post would be incorrect
sdstuber,
Yours works like this:
regexp_like(name,'[%|_]{2}')
paquicuba,

no
regexp_like(name,'[%|_]{2}')  

would search for two-character combinations of the three characters %,  |, and _

if you want to use "or" operator you would use () instead of []

regexp_like(name,'(%|_){2}')


so, given the sample data above

regexp_like(name,'[%|_]{2}')    would "appear" to work, but would really be false-postives since it includes the possibility of "aaaa|%"  which would not be correct
Oops! You're right. I just copied your code w/o noticing the brackets. My point is that your idea will work by introducing the vertical pie ( | ) opeartor within the grouping ( ) operator.
the original would work too, and the (%|_) will also work but they have the same caveat mentioned to awking00 above
Avatar of gram77

ASKER

insert into dummy values ('abc__def%%'); --I want to see this string
insert into dummy values ('abc__def%%ghi%_jkl'); --and not this string in the result

Now i want to see the data which has only __ (2 underscore) and %% (2 ampersands) in it, and nothing else
if you are changing the question you should close this one and open a new one
Avatar of gram77

ASKER

Excellent
Avatar of gram77

ASKER

Thank you all for your efforts, awking00 solution was the simplest and most elegant so i chose it.