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

Question

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

Already answered here: http://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string. Use Query Results to Text... — smoore4, Mar 10 '15 at 10:29
Read the linked question. You also need `char(10)`. The output also depends on how you print it. — CodeCaster, Mar 10 '15 at 10:47
Please stop down voiting me. I tested before :`{CHAR(13),CHAR(10), CHAR(10)+ Char(13, '\n')}. All of them did not work for me` . — Med.Amine.Touil, Mar 10 '15 at 10:53
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. — CodeCaster, Mar 10 '15 at 10:55
`CHAR(13)+CHAR(10)` deifinitely 'works'. But you need to define 'works'. Start by trying a very simple example and work your way up. You might have a bug in you stored procedure OR... you haven't mentioned how you're displaying this. In another application? — Nick.Mc, Mar 10 '15 at 10:56
haha you're right..... I'll change my comment.... not that it seems to help much — Nick.Mc, Mar 10 '15 at 11:00
All of them does not work in a `simple SELECT` statment guys — Med.Amine.Touil, Mar 10 '15 at 11:04
in Settings set the checkbox "Retain CR/LF on copy or save" checked and restart SSMS: https://stackoverflow.com/a/61119705/5308054 — Dzmitry Paliakou, Apr 09 '20 at 11:19

score 3 · Accepted Answer · edited May 23 '17 at 10:30

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.

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. — Med.Amine.Touil, Mar 10 '15 at 11:32

Peter · Answer 2 · 2015-03-10T15:47:35.130

2

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.

edited Mar 10 '15 at 15:47

answered Mar 10 '15 at 11:00

Peter

27,590
8
64
84

score 0 · Answer 3 · answered Nov 06 '19 at 14:46

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;

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

3 Answers3