gram77
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');
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');
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;
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}' )
select * from dummy where regexp_like(name,'[&_]{2}'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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."
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
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} ')
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} ')
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
regexp_like(name,'[%|_]{2}
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
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
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
ASKER
Excellent
ASKER
Thank you all for your efforts, awking00 solution was the simplest and most elegant so i chose it.