?
Solved

Finding strings with special characters in oracle

Posted on 2010-03-31
17
Medium Priority
?
496 Views
Last Modified: 2013-12-18
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');
0
Comment
Question by:gram77
  • 8
  • 3
  • 2
  • +2
17 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29204165
By 'double' do you mean side by side or just that it has two or more somewhere?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 29204352
select * from dummy where regexp_like(name,'[%_]{2}')
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29204442
If side by side try:
select name from dummy where instr(name,'%%') > 0 or instr(name,'__') > 0;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 74

Expert Comment

by:sdstuber
ID: 29204636
you said "ampersand" above but you showed percent '%'  if you meant ampersand then

select * from dummy where regexp_like(name,'[&_]{2}')
0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 29234492
See attached.
select name from dummy
where regexp_like(name,'%%|__|%_');

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 29234815
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

0
 
LVL 32

Expert Comment

by:awking00
ID: 29238687
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."
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 29239467
fair enough  :)

and if it is the case that _% is not legal, then my first (and followup) post would be incorrect
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 29240580
sdstuber,
Yours works like this:
regexp_like(name,'[%|_]{2}')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 29241109
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}')


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 29241331
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
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 29243314
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 29243420
the original would work too, and the (%|_) will also work but they have the same caveat mentioned to awking00 above
0
 

Author Comment

by:gram77
ID: 29314045
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 29332048
if you are changing the question you should close this one and open a new one
0
 

Author Closing Comment

by:gram77
ID: 31709381
Excellent
0
 

Author Comment

by:gram77
ID: 29339086
Thank you all for your efforts, awking00 solution was the simplest and most elegant so i chose it.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month3 days, 19 hours left to enroll

599 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