none
How to add new line in sql server 2008 db

    Question

  • hi all,

    I have an address column. some data from the address has to come in the next line. i wonder if there are any new line charector that i can put directly to the database to achive this (like '\n'). The datatype of the column is "text"

    Tuesday, February 21, 2012 9:20 AM

Answers

  • Use a tool such as SQL Server Management Studio and execute the following inserting the next line character char(13) in as needed:

    CREATE TABLE Address
    (address varchar(100));

    INSERT Address (Address) VALUES (
    'Roma victa'  + char(13) + 'flat no 30' + char(13) + 'Dera' + char(13) + 'Dubai');

    Now if you execute:

    SELECT * FROM Address

    with the Query Editor in "Results to Grid" mode you get:

    Roma victa flat no 30 Dera Dubai

    But if you switch to "Results to Text" mode you get

    Roma victa
    flat no 30
    Dera
    Dubai

    This demonstrates that the next line character is stored in the database. Your problem now is how to get your presentation program to read and implement the next line.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Naomi NModerator Tuesday, February 21, 2012 4:54 PM
    • Marked as answer by roma_victa Wednesday, February 22, 2012 6:25 AM
    Tuesday, February 21, 2012 4:50 PM
    Answerer

All replies

  • Hi,

    Please try with the below code

    DECLARE @TEX TABLE(ID INT,MYADDRESS TEXT)
    INSERT INTO @TEX VALUES(1,'WISH YOU HAPPY BIRTHDAY')
    SELECT REPLACE(CONVERT(VARCHAR(MAX),MYADDRESS), ' ', CHAR(13)) FROM @TEX


    • Edited by svgSuresh Tuesday, February 21, 2012 10:03 AM
    Tuesday, February 21, 2012 10:00 AM
  • Hi SvgSuresh,

    Thanks for replaying . What i ment was like this

    Roma victa

    flat no 30

    Dera

    Dubai

    but it is storred in the database Roma victa flat no 30 Dera Dubai

    is there any way that i can mannually put enter character in it

    so that while showing it in .net it is shown in diffrent lines insted of one

    Thanks

    Roma victa

    • Edited by roma_victa Tuesday, February 21, 2012 2:40 PM
    Tuesday, February 21, 2012 2:38 PM
  • For the ease of use, why dont you split the address column in to multiple columns like.

    address line 1

    address line 2

    city

    state

    country

    postalcode

    It is a better practice to have it in different columns.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

    Tuesday, February 21, 2012 2:48 PM
  • Use a tool such as SQL Server Management Studio and execute the following inserting the next line character char(13) in as needed:

    CREATE TABLE Address
    (address varchar(100));

    INSERT Address (Address) VALUES (
    'Roma victa'  + char(13) + 'flat no 30' + char(13) + 'Dera' + char(13) + 'Dubai');

    Now if you execute:

    SELECT * FROM Address

    with the Query Editor in "Results to Grid" mode you get:

    Roma victa flat no 30 Dera Dubai

    But if you switch to "Results to Text" mode you get

    Roma victa
    flat no 30
    Dera
    Dubai

    This demonstrates that the next line character is stored in the database. Your problem now is how to get your presentation program to read and implement the next line.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Naomi NModerator Tuesday, February 21, 2012 4:54 PM
    • Marked as answer by roma_victa Wednesday, February 22, 2012 6:25 AM
    Tuesday, February 21, 2012 4:50 PM
    Answerer
  • There is no way to insert CHAR(13) directly using Edit 200 top rows. However, you can always use INSERT / UPDATE command to insert the char(13). Also, you will not see it using output to grid option of the query results. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, February 21, 2012 4:55 PM
    Moderator
  • I am confused my the carriage return line feed combination because I usually see a Char(13) + Char(10) if I view a text file in Hex Mode. In SQL-Server, it seems like any one of any combination of these characters will work. For instance, all three of the following examples printed on two lines. Why is that?

    DECLARE @NewLineChar AS CHAR(1) 
    Set @NewLineChar = CHAR(13)
     PRINT ('This is Line One.' +@NewLineChar + 'This is Line Two.' )
     GO
    DECLARE @NewLineChar AS CHAR(1) 
    Set @NewLineChar = CHAR(10)
     PRINT ('This is Line One.' +@NewLineChar + 'This is Line Two.' )
     GO
    DECLARE @NewLineChar AS CHAR(2) 
    Set @NewLineChar = CHAR(13) + CHAR(10)
     PRINT ('This is Line One.' +@NewLineChar + 'This is Line Two.' )
     GO


    Ray Pietrzak

    Monday, August 19, 2013 4:25 PM
  • The presentation of the results depends on the behavior designed into the client application that is running the code example. For instance, the first example doesn't behave the same when using sqlcmd.exe. Management Studio makes different assumptions than sqlcmd.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, August 19, 2013 5:31 PM
    Answerer