1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to replace ',' with new line/?

Discussion in 'SQL PL/SQL' started by Vicky, Apr 6, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi..

    How to replace ',' with new line/, for the below select query?

    SELECT replace('a,b,c',',',chr(10)) FROM DUAL;

    it's giving o/p as

    abc

    But,.I wanna get o/p as

    a
    b
    c

    ..
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    401
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I won't presume to have a solid understanding of pattern matching, but have you looked at using REGEXP_REPLACE as an alternative?

    CJ
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    401
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    In part it depends on how the tool you are using interprets the results. However, CHR(10) is just LF/Line Feed.
    You might try using CRLF (Carriage Return/Line Feed):

     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,176
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Vicky,

    SELECT replace('a,b,c',',',chr(10)) FROM DUAL;

    This is giving correct output which you expected, check it in SQL *Plus.

    If you are using Sql Developer, copy the output and paste it some where, then you may understand what it is coming.
     
    Vicky likes this.
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Alternately use the "Run Script" command rather than "Run Statement".
     
    Vicky likes this.
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    TKS for ur rply guys,., Cud U tel me why it wrks only with "Run Script" but not with "Run Statement"..
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    As I alluded to in my initial post, part of how results are displayed the data includes Carriage-Return/Line-feed characters depends on how the interface is designed to show them. The 'Run Statement" command is not designed to display a single row of results as multiple lines. The "Run Script" uses a different display logic that *is* designed to handle a single row being displayed with multiple lines. This is just the way the SQL*Developer interface is built. There are other query tools that will likewise not display CR/LF in the results.
     
    Vicky likes this.