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