CODEWITHSUNDEEP
sqlregexoracle-database
upog
upog

Reputation: 5531

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

Upvotes: 4

Views: 7702

Answers (2)

Gary_W
Gary_W

Reputation: 10360

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.

Upvotes: 4

Marmite Bomber
Marmite Bomber

Reputation: 21085

First you simple get rid of the spaces using replace.

In the second step you must remove repeated new line characters - replacing them with only one new line character - user regexp_replacefor this purpuse.

Note that you will need posible adjusing of the new line dependent of your OS. Similar is valid for the blanks if there are tabs or other white stuff. 

with dta as (
select 'test1
    test2
  test3



test4' as txt from dual)
select regexp_replace(replace(txt,' ',null),chr(10)||'+',chr(10)) txt from dta;

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

Note, that you still get only one row as a result - if you want a four rows result, you must additionaly split the string on the new line see e.g. here

Upvotes: 3

Related Questions