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

db<>fiddle here