oracle: remove only blank lines

How to remove empty lines in sql or plsql Input

select 'test1
    test2
  test3



test4' from dual;

Expected output:

test1
test2
test3
test4

TRANSLATE is removing all the new line character

Answer

You can do it in one regexp_replace. Specify the arguments for multiline mode and just replace zero or more whitespace characters at the start of the line with nothing. This way only spaces at the start of the lines, if any, are removed so embedded spaces (maybe between words) are preserved, if that’s what you want.

with data(txt) as (
select 'test1
    test2
  test3



test4' from dual
)
select regexp_replace(txt, '^s*', null, 1, 0, 'm') txt 
from data;

Output:

TXT                    
-----------------------
test1
test2
test3
test4                 


1 row selected.