Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Regexp_replace keep alphanumeric and newline

PugzlyDec 29 2021

I am trying to remove all non-alphanumeric characters from a string. 

The caveat is I also want to leave ONLY ONE space between words and KEEP all newlines.

I am using the below query, which almost works, but I can't seem to modify it to keep the new lines.

I have tried using chr(10) and a hex representation of newline but I wasnt successful. Any help would be greatly appreciated.

CREATE TABLE t ( seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
nm VARCHAR(50));

insert into t ( nm) VALUES 
('-AB+      ÷÷÷@@123');
/
insert into t (nm) VALUES 
('ABC.   122#@!' || chr(10) || 'Xxx....');
/

select * from t;

SEQ_NUM    NM
1    -AB+      ÷÷÷@@123
2    ABC.   122#@!
Xxx....


update t 
set    nm = regexp_replace(
         regexp_replace(nm, '[^A-Za-z0-9 ]', ''),
         ' {2,}', ' '
       );

select * from t;

SEQ_NUM    NM
1    AB 123
2    ABC 122Xxx
This post has been answered by Frank Kulash on Dec 29 2021
Jump to Answer
Comments
Post Details
Added on Dec 29 2021
4 comments
4,578 views