Post reply

How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

  • February 15, 2017 at 2:28 am

    #335290

    Hi ALL,

    My table one column have a string like
    'Hai,Hello,How are you,Hai all'

    but when we select the column that time i want display the string like

    1.Hai
    2.Hello
    3.How are you
    4.Hai all

    please help me asap

  • February 15, 2017 at 2:35 am

    #1928573

    Are you looking for four separate rows, or a single row with three line breaks in it?

    John

  • February 15, 2017 at 2:46 am

    #1928574

    same row

  • February 15, 2017 at 3:05 am

    #1928576

    Quick suggestion
    😎

    DECLARE @NL   CHAR(2)  = CHAR(13) + CHAR(10);
    DECLARE @MYSTRING VARCHAR(MAX) = REPLACE('Hai,Hello,How are you,Hai all',CHAR(44),@NL);
    SELECT @MYSTRING;

    Output
    ------------
    Hai
    Hello
    How are you
    Hai all

  • February 15, 2017 at 3:09 am

    #1928577

    Probably the best way is to split it into individual rows using a splitter function, which will also give you the row number, then reconcatenate with a SELECT...FOR XML statement.  Don't forget to slip in CHAR(10) and/or CHAR(13) for the line break.  (If you don't really need the numbers at the start of each line, Eirikur's solution will work fine.)

    John

  • February 16, 2017 at 3:29 pm

    #1928999

    The issue that you're seeing is that the results you get depend on what user interface you are using.  Specifically, the grid view in SSMS returns different results than the text view.  The grid view will convert line breaks to a space, but the text view will display the line breaks.  Similarly, the grid view will appear to truncate strings that contain the nil character (0x0), but the text view will display the whole string.  These are limitations of the grid view in SSMS.  Since the grid view should never be used as a presentation layer, you shouldn't tailor your solution to get the expected results in grid view.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 16, 2018 at 11:17 pm

    #2002184

    But in my case I'm not getting output like that u mentioned.

  • August 17, 2018 at 1:10 am

    #2002190

    karthiklagudu - Thursday, August 16, 2018 11:17 PM

    Eirikur Eiriksson - Wednesday, February 15, 2017 3:05 AM

    But in my case SQL server 2016, i Did not get result like this

    This works perfectly in SQL Server 2016, but the SSMS must be set to display the results as text, use CTRL+T before running the query.
    😎


    DECLARE @IPSTR VARCHAR(200) = '123.56,45.873,4.5,4.0,45768.9,354.67,12.0,66.97,45,4.5672';

    -- MULTI LINE STRING OUTPUT, PRESS CTRL+T TO DISPLAY THE RESULTS AS TEXT.
    SELECT
      REPLACE(@IPSTR,CHAR(44),CHAR(13)+CHAR(10)) AS MULTI_LINE_STRING;

    -- SPLIT COMPARISON
    SELECT
      SP.value
    FROM STRING_SPLIT(@IPSTR,CHAR(44)) SP;

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply