Solved

Oracle SQL - Removing Alphanumeric Characters from Strings

Posted on 2013-11-19
2
1,220 Views
Last Modified: 2013-11-19
Hi,

I'm trying to search in my name field for the records with no alphanumeric values.
I want to find the records with only null value or only special characters such as . , ? ! * % #.

For example,

ID       Name
----     ----------
1         James Brown??..
2
3         ...
4        >,*..??
5        1/James Brown***

Then, I want my code to return only ID 2,3,4 and not 1,5.
Also, I need to put this check in a case statement, rather than in a where clause.

Can you please help me how I can write this check using regexp_like or regexp_replace?

Thank you!
0
Comment
Question by:iamnamja
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 39659345
Try this:

drop table tab1 purge;
create table tab1 ( id number, col1 varchar2(100));

insert into tab1 values(1,'James Brown??..');
insert into tab1 values(2, null);
insert into tab1 values(3,'...');
insert into tab1 values(4,'>,*..??');
insert into tab1 values(5,'1/James Brown***');
commit;

select id from tab1 where not regexp_like(col1,'[A-Za-z0-9]') or col1 is null;

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 39659359
select id from your_table where regexp_like(name,'^[.,?!*%#]+$') or name is null;

simply add whatever characters you want into the [] list
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

730 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