0

SOLVED: Thanks folks!

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.

APC
  • 144,005
  • 19
  • 170
  • 281
Allen
  • 21
  • 1
  • 4
  • A variant of this question (replacing CR/LF with a space) which I ran into some time ago is here, http://stackoverflow.com/questions/16407135/oracle-regexp-to-replace-n-r-and-t-with-space. It does have the ASCII hexadecimal approach. There are a lot of similar posts like this, btw. – Patrick Bacon May 07 '15 at 21:37
  • "SOLVED"? If one of the answers posted here solved your problem please accept it. If you solved it with a different approach you should post it yourself and accept that. Please help make SO a valuable resource for other seekers. – APC May 09 '15 at 06:55
  • " If one of the answers posted here solved your problem please accept it." Happy to do that but not seeing how to do that. I can't 'vote up' if that's what you mean without more 'points.' – Allen May 11 '15 at 19:32

4 Answers4

3

Remove the quotes from around the CHR() function calls:

with tbl(text) as
(
select 'blah blah blah; and more stuff; and even more;' from dual
)
select replace(text, ';', chr(13)||chr(10)) from tbl;

EDIT: This will replace the semi-colon and following whitespace if it exists:

with tbl(text) as
(
select 'blah blah blah; and more stuff;and even more;' from dual
)
select regexp_replace(text, ';\s?', chr(13)||chr(10)) from tbl;
Gary_W
  • 9,933
  • 1
  • 22
  • 40
1

Don't put quotes around the CHR(13)||CHR(10) in your REPLACE.

REPLACE (XYZ, ';', CHR(13)||CHR(10) )

That said, whatever front end you have to display the data would need to be able to read and interpret those characters the way you want (i.e. visually creating a new line). There's no guarantee that your front end will do that rather than displaying them as unprintable characters or ignoring them.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0
select replace(xyz, ';',  chr(10)) from dual
Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28
0

Don't put quotes. Of course. Thanks! I'd vote up but I don't have those powers yet. (Woke up in the middle of the night, last night, with the same realization.) Thanks Everyone! 

REPLACE (XYZ, ';', CHR(13)||CHR(10) )

I'm stuck dealing with user comments (from several users over time) in a jammed-together sequenced 255 char rows (where it cuts off even in mid-word, but each user's comment ends in a semi-colon) so...

REGEXP_REPLACE (
            (LISTAGG (ENG2.VALUE, '') WITHIN GROUP (ORDER BY ENG2.VALUE)),
            ';',
            ( CHR (13) ||  CHR (10) ))

Then I put that inside a word wrap function. Works great. Looks good. Thanks again!

Actually, that didn't work so well (because I'm adding CR/LF then adding it again,) so I put the REPLACE of the semi-colon within the word-wrap function and created a special option 'O2' to make that happen.

  SELECT 
     ' ' ||    F_WORD_WRAP_WF (
            (LISTAGG (ENG2.VALUE, '')
                WITHIN GROUP (ORDER BY TO_CHAR (ENG2.SEQ) || ENG2.VALUE)),
            76,
            'O2')
            AS Approver_Comment

then in the word wrap function:

... ending an if statement with this:

  ELSIF p_wf_ob = 'O2'
  THEN
     line :=
        SUBSTR (
              REGEXP_REPLACE (NVL (p_textin, ' '),
                              ';',
                              CHR (13) || CHR (10))
           || pad,
           toffset,
           p_rmargin);
Allen
  • 21
  • 1
  • 4