Oracle SQL | Use regex to ignore control characters except new line?

Question

I need to identify and replace all the control characters in a column with '' I tried using following query.

 SELECT description, REGEXP_REPLACE(description, '[![:cntrl:]]', '') from table1;

It removes all the control characters along with the new line character. I need to sustain the new line character. I tried using something like following.

 REGEXP_REPLACE(description, '[![:cntrl:]'||CHR(10)||CHR(13)||']', '')

But it didn't work. I would appreciate any help in right direction. Thanks in advance.

can you share more information or the sample data vs expected data ? — Nizam - Madurai
– Nizam - Madurai, Commented Oct 16, 2018 at 12:35

Alex Poole · Accepted Answer · 2018-10-16 12:24:26Z

4

:cntrl: is the opposite of :print:, so you could negate the combination of that plus the control character(s) you want to keep:

regexp_replace(description, '[^[:print:]'||chr(10)||']', null)

or possibly, based on your attempt (but not needed if you did only mean 'new line'):

regexp_replace(description, '[^[:print:]'||chr(10)||chr(13)||']', null)

The negation is done by ^, not !. Your original code was removing any control characters or the actual character !, which probably wasn't quite what you meant.

edited Oct 16, 2018 at 12:24

answered Oct 16, 2018 at 12:19

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL | Use regex to ignore control characters except new line?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related