Update strings containing special characters

tonMachine100 used Ask the Experts™
I Have a client name field which contains some characters which need deleting.

The characters which need deleting are characters which ARE NOT in this list:

' -0123456789abcdefghijklmnopqrstuvwxyz

so for example the field might be

Mar£k - to be updated to - Mark
St&ephe[n - to be updated to - Stephen

any help is appreciated
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try like this :

Taking an example :

--create table 
create table test_temp (id number, name varchar2(80));

-- insert record 
insert into test_temp values(1,'Ma$rk') ;
insert into test_temp values(2,'Mar£k');
insert into test_temp values(3,'St'||'&'||'ephe[n');

--commit it .. 


Open in new window

--Update the statement ..
SET NAME = TRIM ( REGEXP_REPLACE ( NAME , '[£[{}\*;%&$´`€_.+,-]*' ) ) ;


Open in new window

Finally check the result
Most Valuable Expert 2012
Distinguished Expert 2018
>>' -0123456789abcdefghijklmnopqrstuvwxyz

I assume you also want capital letters?

Using the same regexp_replace but replaing everything except what you want to keep:
select regexp_replace('M''a-r£k','[^0-9A-Za-z''-]') from dual;
Information Technology Specialist
You can also use the character class -
select regexp_replace(yourfield,'([^[:alnum:]])') from dual;
Note - if you want ampersands removed, you will need to set define off.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial