How to replace the new line and line feed in a specific manner

Question

I have the below data in a column called as "DATA" in oracle.I was trying to remove the new empty line after each line .

• Input

  This is a text from line 1.
  
  This is text from line 2.
  
  This is a text from line 3.The line 3 ends here .
  
  This is a text from line 4.The line ends here .
  

• output

  This is a text from line 1.
  This is text from line 2.
  This is a text from line 3.The line 3 ends here .
  This is a text from line 4.The line ends here .
  

I have tried using

Query :

Select regexp_replace(regexp_replace(data,chr(13),null),chr(10),null) from main_data;

When i execute the below query the output is like a paragraph .

This is a text from line 1.This is text from line 2.This is a text from line .The line 3 ends here .This is a text from line 4.The line ends here .

Can any one say how to achieve this ?

Answer 1

You may use a '(' || chr(13) || chr(10) || '?|' || chr(10) || '){2,}' regex:

select regexp_replace(
    'This is a text from line 1.' || chr(13) || chr(10) || chr(13) || chr(10) || 'This is text from line 2.' || chr(10)  || chr(10) || 'This is a text from line 3.The line 3 ends here .'  || chr(10) || chr(10) || 'This is a text from line 4.The line ends here .',
    '(' || chr(13) || chr(10) || '?|' || chr(10) || '){2,}',
    '\1') as Result from dual

The pattern matches 2 or more ({2,}) consecutive repetitions of a CR symbol followed with an optional (1 or zero, ?) LF symbol or (|) an LF symbol.

The match is replaced with the single, last matched occurrence of CRLF, CR or LF as \1 is a placeholder for the value captured with the capturing group #1 (the first (...) construct in the pattern).

Output of the online demo: