vendredi 8 mai 2015

Oracle SQL: How to replace a character in an existing VARCHAR2 field with CR/LF

I'm selecting from a field that has a bunch of ugly jammed-together text where each 'section' ends in a semi-colon. What's the method to replace the semi-colon with line-feed 0d0a (crlf) "non-printable" characters? I've searched but most people want the opposite (to remove CR/LF from existing text.)

Example: The existing Varchar2 field XYZ contains

'blah blah blah; and more stuff; and even more;'

(This is not something I control, I just have read access to it.)

If I REPLACE (XYZ,';',' CHAR(13)||CHAR(10)) ') on output the output is just this with no active line feeding. It literally adds the characters CHAR(13)||CHAR(10)) and not the function of a CR/LF. Like this:

Select REPLACE(XYZ',';','CHAR(13)||CHAR(10)') from XYZTable;

Unwanted Result:

'blah blah blah CHAR(13)||CHAR(10)) and more stuff CHAR(13)||CHAR(10)) and even more CHAR(13)||CHAR(10))'

What I'd like to see in an output:

blah blah blah
and more stuff
and even more

Really hoping there's a way to insert HEX or something. Or am I stuck doing a loop? Rather not, this is a construction from separate lines with sequences where the table designer should have used a CLOB and I'm using LISTAGG to paste it together.

Aucun commentaire:

Enregistrer un commentaire