Line break in T-sql (Char(13)) is not working

sql server preserve line breaks
t-sql for xml path line break
new line character in sql server string
how to find new line character in sql
replace comma with newline in sql server
char(13) + char(10)
how to insert new line character in sql query
find carriage return in sql

I am using a T-sql (Sql server 2008) stored procedure which returns a custmized values based on many business conditions. (This must be done on database because i have a generic call to many stored procedures from C# code). My issue here is with the line-break. I use in my code Char(13);Char(13) + char(10); char(10)... but it doesn't work.

NB: If i test an INSERT statment it works but in the SELECT statment no line break ,it generates a White space (Done on a simple query). Bellow my code

ALTER PROCEDURE [dbo].[getDebtorAddress] (
    @idFolder int 

)   
AS
BEGIN
DECLARE
    @type bit  ,
    @firstName varchar(100) ,
    @familyName varchar(100) ,
    @raisonSocial varchar(100),
    @jeuneFille varchar(100) ,
    @saleName varchar (100) ,
    @enseigne varchar (100) ,
    @sigle varchar (100) ,
    @address varchar (max),
    @lieuDit varchar(100) ,
    @postalCode varchar (100),
    @city varchar (100),
    @country varchar (100),
    @finalAddress nvarchar(max)


    SET NOCOUNT ON;
    SELECT DISTINCT 
    @firstName = Actor.M_EN_NOM,
    @familyName = Actor.M_EN_PRENOM1,
    @raisonSocial = Actor.M_EN_RAISONSOCIALE,
    @jeuneFille = Actor.M_EN_NOMJEUNEFILLE,
    @saleName = Actor.M_EN_NOMCOMMERCIAL,
    @enseigne = Actor.M_EN_ENSEIGNE,
    @sigle = Actor.M_EN_SIGLE,
    @address = ActorCP.M_PR_CP_ADRESSE,
    @lieuDit = ActorCP.M_PR_CP_LIEUDIT,
    @postalCode = PostalCode.C_PA_CP_CODEPOSTALE,
    @city = PostalCode.C_PA_CP_VILLE,
    @country = Country.C_PA_PA_LIBELLE,
    @type = Actor.M_EN_TYPE


    FROM M_EN Actor
    LEFT OUTER JOIN M_DE Debtor ON Actor.M_EN_ID =Debtor.M_EN_ID  
    LEFT OUTER JOIN M_DO Folder  ON Debtor.M_DE_ID= Folder.M_DE_ID
    LEFT OUTER JOIN M_PR_CP ActorCP ON Actor.M_EN_ID = ActorCP.M_EN_ID
    LEFT OUTER JOIN C_PA_CP PostalCode ON ActorCP.C_PA_CP_ID = PostalCode.C_PA_CP_ID
    LEFT OUTER JOIN C_PA_PA Country ON ActorCP.C_PA_PA_ID = Country.C_PA_PA_ID
    WHERE Folder.M_DO_ID = @idFolder

    if(@type=0)
         begin
            if (@firstName is not Null and @firstName !='')
                set @finalAddress= @firstName
            if(@familyName is not Null and @familyName != '')
                set @finalAddress = @finalAddress + ' ' + @familyName +CHAR(13)
            if(@jeuneFille is not null and @jeuneFille !='' )
                set @finalAddress=@finalAddress + ' ' + @jeuneFille + CHAR(13)
            if(@address is not null and @address != '')
                set @finalAddress=@finalAddress +  REPLACE(@address,'|',char(13)) 
            if(@lieuDit is not null and @lieuDit != '')
                set @finalAddress = @finalAddress  + @lieuDit + CHAR(13)
            if(@postalCode is not null and @postalCode != '')
                set @finalAddress = @finalAddress  + @postalCode + CHAR(13)
            if(@country is not null and @country != '')
                set @finalAddress = @finalAddress  + @country + CHAR(13)
         end
    else
        begin
            if (@raisonSocial is  Null or @raisonSocial = '')
                Begin
                    if(@firstName is not null and @firstName != '')
                        set @finalAddress = @finalAddress  + @firstName + CHAR(13)
                    if(@familyName is not null and @familyName != '')
                        set @finalAddress = @finalAddress  + @familyName + CHAR(13)
                end
            else
                set @finalAddress  =  @finalAddress + @raisonSocial + CHAR(13)
             if(@jeuneFille is not null and @jeuneFille !='' )
                set @finalAddress=CHAR(13) + @finalAddress + ' ' + @jeuneFille + CHAR(13)
            if(@saleName is not null and @saleName != '')
                set @finalAddress=@finalAddress +  @saleName + CHAR(13)
            if(@enseigne is not null and @enseigne != '')
                set @finalAddress=@finalAddress +  @enseigne + CHAR(13)
            if(@sigle is not null and @sigle != '')
                set @finalAddress=@finalAddress +  @sigle + CHAR(13)
            if(@address is not null and @address != '')
                set @finalAddress=@finalAddress +  REPLACE(@address,'|',char(13))   
         end

         Create Table #TempAddress 
         (
          TempAddress_ID  int ,
          adresse nvarchar(max)
         )
         INSERT INTO #TempAddress (#TempAddress.adresse) VALUES(@finalAddress)
         SELECT #TempAddress.adresse from #TempAddress
         DROP TABLE #TempAddress
END

the current result is

aaaaa bbbbb ccccc dddddd

but my expected result is

aaaaa 
bbbbb
ccccc
ddddd

Any suggestion or help ? Thanks

As I explained in my comment, on Windows a newline is CHAR(13)+CHAR(10), but whether that is rendered as a newline depends on how you print it.

in the SELECT statment no line break ,it generates a White space [...] does not work in a simple SELECT

"simple SELECT" does not explain what you're trying to do. Please rember that you are the only one here that can see your screen. You need to be explicit if you want others to understand your problem.

If you mean that the grid view in SQL Server Management Studio doesn't display newlines, you're correct: it doesn't, until you click "Results to Text" button in the toolbar as suggested in the first comment.

Sql Server 2017 does not output chat(10) or char(13) � Issue #407 , Hi guys, I just migrated from sql server 2014 to sql server 2017. select 'hi' + char(10) + char(13) + 'people' I am not getting any line break. GitHub is home to over 50 million developers working together to host and review� char(13) not working as intended bmacbmac (IS/IT I would love to actually make each string on it's own line in the results window so I can just save the file as I

Your T-SQL is working fine it is inserting char(13), however depending on your client you need to add a extra char(10).

 char(13) + char(10)

It is probably a better idea to do the formatting like this at client side and not in your sql query.

Carriage return/line feed stopped working in SQL Server, Since you're outputting to Grid View, I suspect that the option to Retain CR/LF on copy or save is disabled. Enable this option, open a new query window, run the� New Line / Line Break: Char(10) Carriage Return: Char(13) Tab: Char(9) Inserting Line Break or New Line. In this example, I’ve generated a string with comma delimiters. Then the commas are replaced by the line break character CHAR(10). If you notice the result, it’s formatted with line break and finally it says 1 rows affected.

You can also use this syntax:

......note this last single quote after line 2...

                                  |  -> press enter and begin a new line with another...
SELECT 'use ' + DB_NAME() + ';' + '
    ' + 'CREATE USER ' + users.name + ' for ' + users.name + ';'
    | -> continued quote in line 3. 

This will produce the following output:

use MyDatabase;

    CREATE USER User1 for User1;

SQL Carriage Returns or Tabs in SQL Server strings, This article explores inserting SQL carriage return, line break and tab in a We work with various data types in SQL Server such as int, float, XML, char to notepad or excel sheet, SQL Server does not retain SQL carriage We use the Char(13) function for inserting a carriage return instead of a line break:� We use Char (13) for identifying and removing Carriage Return and Char (10) for removing line break along with the SQL REPLACE function. The replace function replaces line break with a space as specified in the query: Select EmpID, FirstName, lastName, REPLACE(Address,CHAR(13)+CHAR(10),' ') as address from EmployeeData

CHAR(10) & CHAR(13) in SQL Server Columns, CHAR(10) & CHAR(13) in SQL Server Columns. Posted: May 2, 2019 sqlserver. Recently I needed to export some data from a table that was being used to hold would be formatting issues and text appearing on a new line when in SQL it appeared that all of CREATE TABLE #NewLine ( ID INT IDENTITY(1,1) NOT NULL� Your T-SQL is working fine it is inserting char(13), however depending on your client you need to add a extra char(10). char(13) + char(10) It is probably a better idea to do the formatting like this at client side and not in your sql query.

[SOLVED] SQL Update with Carriage Returns, CHAR(13) + CHAR(10) UPDATE PART_BINARY SET BITS=CAST('XXXXX' AS VARBINARY(8000)) WHERE But if you PRINT it, you'll see the line break. The problem is that the line break is not showing up in the field within the ERP. Seems to work with varbinary as well? SQL. CREATE TABLE #crlf ( idx INT,� SELECT 'line 1 line 2 line 3' or. SELECT 'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3' With embedded CRLF, the display in the grid appears to replace them with spaces (I guess so that they will display all the data). The problem is that if I am code-generating a script, I cannot simply cut and paste this.

Difference between Line Feed (\n) and Carriage Return (\r), Line Feed – LF – \n – 0x0a – 10 (decimal). Carriage Return – CR – \r – 0x0D – 13 (decimal) Example 1: No new line feed char CHAR(10) alone used to work for me in SSMS queries and with latest SSMS both CHAR(10)� This is the First Line Second Line ‘ That permits me to Indent all the lines as much as I need. As about the second example, as David Rosario pointed out, is a windows specific solution. That is why I use both chars as line break: Declare @LineBreak nvarchar(10) = char(13)+ Char(10), @text nvarchar(100);

Comments
  • Already answered here: stackoverflow.com/questions/31057/…. Use Query Results to Text...
  • @SQLDBA as i said in my description Char(13) is not working
  • Read the linked question. You also need char(10). The output also depends on how you print it.
  • YOU NEED char(10) + char(13)
  • It is char(13) + char(10), not any of the other contraptions you show. Whether and how the newline is rendered also depends on how you print it. Do you print to the console? A WinForms textbox? Show that code.
  • You are totally right about the result in sql server managment studio. But still have the same problem in C# code. So i used the '\n' instead of CHAR(13). It ll be treated as a break-line.