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