Carriage Return in T-SQL varchar

This turned out to be a dumb question – or rather it had a simple answer.  I wanted to insert a carriage return and line feed into a sql statement I was generating in T-SQL so that it was more readable.  My first question was which comes first, the chicken or the egg?  Just kidding – actually the question was, which comes first char(13) or char(10)?  Turns out, seems like char(13) comes first.  I’ve always been inside a programming language so you could use things like Environment.NewLine – so I hadn’t ever memorized it.  First question answered.

The second question was about actually getting the carriage return to show up in the text.  Turns out that you can’t put (or I couldn’t figure out a way) a carriage return into a statment that is SELECTed.  Rather you need to use the PRINT statment and then you’ll be good to go.

I never found anyone that really stated that you can’t do it in a SELECT and to do it in a PRINT.  So – I thought I’d just get it written down here – if not for my reference later!

Have a good day!

Mark

Advertisements

11 Responses to Carriage Return in T-SQL varchar

  1. pinaldave says:

    Hi Mark,

    Interesting post I enjoyed reading it. I have previously written something similar, you have put this concept very well.

    http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  2. Joe says:

    Funny. Drumming up some more web hits Dave?

    Sure enough Mark, I tried:

    SELECT ‘this’ + CHAR(10) + ‘&’ + CHAR(13) + ‘that’

    …and it comes out “this & that’. When I try this:

    PRINT ‘this’ + CHAR(10) + ‘&’ + CHAR(13) + ‘that’

    …it comes out like:

    this
    &
    that

    …how strange. I’m in a pickle as to how to get a carriage return into a varchar that I pass to Database Mail in SQL 2005. I’ll keep plugging away at it…

    –Joe

  3. Mohammed A. Fadil says:

    This is normal, in order to see the correct output make sure to select output to text, NOT output to grid (the grid doesn’t handle new lines)

  4. daniel says:

    — Try the below
    — this blog changes single quotes into curlies. Change back before running.

    declare @v varchar(max)

    select @v = ‘this’ + CHAR(10) + ‘&’ + CHAR(13) + ‘that’
    select @v
    print @v

    — note, the line breaks here are intentional.
    select @v = ‘this
    &
    that’

    select @v
    print @v

    — The carriage returns are there.

  5. John Deverdits says:

    Hi,

    I’m actually trying to code a SQL statement that will insert text containing a carriage return and line feed into a nvarchar(max) record, but for some reason the CHAR(10)+CHAR(13) don’t seem to do anything. Does anyone know why? here’s my statement

    update loc_category set categoryname=@categoryname, categoryhistory= (select categoryhistory from loc_category where categoryid = @categoryid)+char(10)+char(13)+convert(varchar, getDate(),101)+’ Updated record’ where categoryid = @categoryid

    Everything works except there is no new line before the date entry.

    Thanks
    John.

  6. David says:

    I’m actually having a similar problem as posted on March 21st. Did anyone ever come up with a solution?

    I’m writing an update statement to merge 2 columns. I want to add a hard return in front of the 2nd column to improve readability in the gui for the users. This was the statement with the char(13):

    update defects
    set description = ISNULL(CAST(description AS VARCHAR(max)),’ ‘) + CHAR(13) + ISNULL(cast(replicationprocedures AS varchar(MAX)),’ ‘)

    Unfortunately that doesn’t work, it just sticks the first character from replicationprocedures after the last character from defects.

    Any help would be greatly appreciated 🙂

    Thanks,
    David

    • John Deverdits says:

      Hi David,

      I think in my situation the issue was how I was trying to display the record. I was trying to display it in a label I think and that’s why it never showed the CR. When I changed it to a multi line text field it worked if I recall right. I actually found a different way of doing what I needed and didn’t need this afterall. Hope this helps.

      John.

  7. waeva says:

    @commentors,

    char(13) — only Carriage Return (CR)
    char(10) — only Line Feed (LF)

    line break = CR+LF (in that order)

    SQL Server shows a line break in output even if the text has only CR or only LF, but this is not technically proper. So make sure you use char(13)+char(10), atleast for Windows machines.
    For other machines, see

    Reference: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/a87b9fbb-40dc-4a2e-83df-eab150620122/

  8. Steve says:

    this is a little different, but helpful if you want the text in a field to have carriage returns…

    insert into TH_TEMPLATE_MAP values (‘182′,’Dear Customer,’||chr(13)||chr(10)||chr(13)||chr(10)||’Your internet service has reached….’);

    the ‘||chr(13)||chr(10)||chr(13)||chr(10)||’ will cause the text to appear this way when it is shown on a screen…

    Dear Customer,

    Your internet service has reached…

  9. Gaurav Bhatnagar says:

    When inserting data into the field, use a markup to indicate the line breaks.

    When selecting the data use the ‘replace’ function to replace the with char(13).

    eg.
    Using the AdventureWorks DB, I modified the value of one of the fields in the Production.ProductDescription table
    field value: Aluminum alloy cups; Large diameter spindle.

    sql statement:
    Select Replace((Select [Description] from Production.ProductDescription where ProductDescriptionID=4), ”, Char(13))

    Output:
    Aluminum alloy cups;
    large diameter spindle.

    (1 row(s) affected)

  10. jeff buzhardt says:

    Send the email as HTML and embed

    At the @body argument, have ‘ {….. remainder of body test}’

    In my case, I had data lines, so i had a select statement:
    SELECT ”” + [mydata] FROM [mytable]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: