24

I am trying to select a column from a table that contains newline (NL) characters (and possibly others \n, \r, \t). I would like to use the REGEXP to select the data and replace (only these three) characters with a space, " ".

APC
  • 144,005
  • 19
  • 170
  • 281
Tuti Singh
  • 241
  • 1
  • 2
  • 3

2 Answers2

40

No need for regex. This can be done easily with the ASCII codes and boring old TRANSLATE()

select translate(your_column, chr(10)||chr(11)||chr(13), '    ')
from your_table;

This replaces newline, tab and carriage return with space.

TRANSLATE() is much more efficient than its regex equivalent. However, if your heart is set on that approach, you should know that we can reference ASCII codes in regex. So this statement is the regex version of the above. 

select regexp_replace(your_column,  '([\x0A|\x0B|`\x0D])', ' ')
from your_table;

The tweak is to reference the ASCII code in hexadecimal rather than base 10.

Bela Vizer
  • 2,527
  • 22
  • 26
APC
  • 144,005
  • 19
  • 170
  • 281
  • Thanks. But I dont want to use translate function instead I want to use the REGEXP only. One way I was able to do this was as follows: select regexp_replace ('Hello'||chr(10)||'XYZ', '[[:cntrl:]]', ' ') from dual; Now "[[:cntrl:]]" will replace all non printable characters but I wanted to know how to specify only "\n", "\r" and "\t". – Tuti Singh May 07 '13 at 17:30
  • 2
    TRANSLATE() is much more efficient than its regex equivalent. So why do you want to use regex? – APC May 07 '13 at 20:31
  • 2
    This principle of escaping characters using `\x0A`, `\x0B` etc, doesn't work for me. Oracle 11g. Anyone know of any reasons why? Is there an optimisation setting for this? – cartbeforehorse Aug 03 '16 at 10:16
  • 2
    @APC `TRANSLATE` can't collapse multiple newlines down into a single replacement. I'd also expect it to duplicate the new separator for Windows line endings. The regex version doesn't seem to work for me. It's not replacing anything. This regex seems to work, though: `'(['||chr(10)||chr(11)||chr(13)||']+)'`. (Remove the `+` if you want each instance replaced separately.) – jpmc26 Jun 30 '17 at 23:13
  • 4
    If it's OK to replace _all_ whitespace (including tabs for example), `regexp_replace(text, '\s+', ' ')` will do it. – William Robertson Dec 15 '17 at 10:32
  • 2
    For the regexp_replace, this answer is wrong on two counts. \t is chr(9), \n is chr(10), \r is chr(13), resulting in a regex that should read `\x09|\x0A|\x0D`. The parentheses and square brackets are NOT required either. As it is in the answer given, it will result in a greedy expansion, and other characters will be removed too. – JGFMK Jun 19 '18 at 11:52
1

select translate(your_column, chr(10)||chr(11)||chr(13), ' ') from your_table;

to clean it is essential to serve non-null value as params ... (oracle function basically will return null once 1 param is null, there are few excpetions like replace-functions)

select translate(your_column, ' '||chr(10)||chr(11)||chr(13), ' ') from your_table;

this examples uses ' '->' ' translation as dummy-value to prevent Null-Value in parameter 3

Ma Ha
  • 17
  • 4