Re: Help on Removing "Junk" Characters

Robert wrote:
> 10g
>
> Please help with this:
>
> How to write an UPDATE where REGEXP_REPLACE (or something else) can be used
> to replace any non-alphanumeric characters with NULL ?
>
> Several records have data that look like this:
>
> customer.companyname
> --------------------------------------------------
> Pericles Comidas cl?cas Guillermo Fern?ez
> Queen Cozinha L?cia Carvalho
> Supr?s delices
>
>
> Thanks

Here's a prototype to get you started:

drop table owatest;

create table owatest (

   test varchar2(100)
)
/

insert into owatest values('alphanumerics with other stuff 234% 99 |');
insert into owatest values('alphanumeric only 12387');

select * from owatest;

update owatest
set test = regexp_replace(test,'[^[:alnum:][:space:]]','',1,0) /

select * from owatest;

This is for 10gR1.

With 10gR2 you don't have to use POSIX regex, but can also use Perl type regex. Received on Wed Oct 19 2005 - 01:33:03 CDT