Home > Software design >  How to remove the blank spaces between two lines in a column
How to remove the blank spaces between two lines in a column

Time:12-18

I have a column that returns the following output:

PERSON_NUMBER                                    FEEDBACK
13636                                         -Very attentive during our sessions
                                            
                                              -Very interative session, questions 
                                                were asked and answered well.
                                            


                                              Nice turn over 

                                              Debates were good

I want the output of FEEDBACK Column to look like:

PERSON_NUMBER                                    FEEDBACK
13636                                         -Very attentive during our sessions                                           
                                              -Very interative session, questions 
                                                were asked and answered well.
                                              Nice turn over 
                                              Debates were good

I.e. the extra blank spaces between the lines should be removed.

CodePudding user response:

You could do a regex replacement on (?:\r?\n){2,} and replace with just a single CR?LF:

SELECT PERSON_NUMBER,
       REGEXP_REPLACE(FEEDBACK,
                      '(' || chr(13) || '?' || chr(10) || '){2,}',
                      chr(13) || chr(10)) AS FEEDBACK
FROM yourTable;

CodePudding user response:

If you need to remove all the lines that look empty (e.g. that may also contain spaces, tabs or other non-printable characters), then you may use negation of the [:graph:] class in the regex to find such rows and replace them with empty row and then replace all newline repetitions with single one.

Below is the code:

with a as (
  select q'[-Very attentive during our sessions
                                            
-Very interative session, questions 
 were asked and answered well.]' || chr(13) || chr(10) || q'[
                                            
       ]' || chr(10) || q'[      

]' || chr(10) || chr(10) || q'[Nice turn over]' as q
from dual
)
select
  regexp_replace(
    regexp_replace(
      q,
      /*
        Replace LF followed by any non-printable sequence that ends with newline
        with single newline
      */
      chr(10) || '[^[:graph:]]*(' || chr(13) || '?' || chr(10) || ')',
      chr(10) || '\1'
    ),
    /*Then replace newline repetitions*/
    '(' || chr(13) || '?' || chr(10) || ') ',
    '\1'
  ) as q
from a
q
-Very attentive during our sessions
-Very interative session, questions
 were asked and answered well.
Nice turn over

db<>fiddle here

  • Related