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