Update strings containing special characters

tonMachine100
tonMachine100 used Ask the Experts™
on
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
Comment
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 
begin
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');
end;

--commit it .. 

commit;

Open in new window


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

commit; 

Open in new window


Finally check the result
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>' -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
Commented:
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