none
How to add new line in SELECT Statement RRS feed

  • Pergunta

  • Hi All

            i have a select statement in which i select one column by concatenating two string.. i want to show the value of second string in second line.

    for example

     

    SELECT 'FirstName' + 'LastName' from tbl;

    this statement prints value  "FirstNameLastName"

    but i want to show the data as

    "FirstName
     LastName"

    but dont know how to achieve this.... any one can help plz??


    Muhammad Usman
    • Movido Bob Beauchemin sexta-feira, 24 de dezembro de 2010 16:16 Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    sexta-feira, 24 de dezembro de 2010 11:56

Respostas

Todas as Respostas

  • You can add a carriage return using CHAR(13). For example...

    SELECT 'FirstName' + CHAR(13) + 'LastName'

    It will not show up that way in the data grid, but if you change the setting to "Results to Text" you will see the new line. Here is a link that has a few examples along with other control characters.

    http://msdn.microsoft.com/en-us/library/ms187323.aspx


    Ken Simmons
    http://cybersql.blogspot.com
    http://twitter.com/kensimmons
    • Sugerido como Resposta Naomi NModerator sexta-feira, 24 de dezembro de 2010 18:21
    • Marcado como Resposta Usman.Aridian segunda-feira, 27 de dezembro de 2010 12:41
    sexta-feira, 24 de dezembro de 2010 13:27
  • Instead of concatenating the CHAR function values, you can alternatively include the carriage return and line feed characters in the literal. 

    SELECT 'FirstName
    LastName' 
    FROM dbo.tbl;
    
    
    

    If the FirstName and LastName are columns from the source table:

    SELECT FirstName + '
    ' + LastName 
    FROM dbo.tbl;
    
    

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    sexta-feira, 24 de dezembro de 2010 14:16
  • Thanks for your responses KEN and DAN  but i have try all the above mentioned solutions but none of the above is worked....

     

    KEN: i have to show the data to the grid format.... so any solution that work for data grid format will be useful for me.

    DAN: yes the columns are from source table. and if i try your solution than it just add space not new line... and the space depends on how much space i mentioned in the select statement after the first column and nothing more.


    Muhammad Usman
    segunda-feira, 27 de dezembro de 2010 05:23
  • You can not see char(13) in SSMS in the Grid format. This character is there and you can see it if you output the result into report, into text, into Excel. But in SSMS grid you can not see this character.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marcado como Resposta Usman.Aridian segunda-feira, 27 de dezembro de 2010 12:41
    segunda-feira, 27 de dezembro de 2010 05:25
    Moderador
  • SELECT 'FirstName' +CHAR(10)+ 'LastName' from tbl;

    Above command not work in grid but in rdlc report grid it will work 100%

    Thank you

    segunda-feira, 23 de maio de 2016 03:37
  • This will only work when you see the result in ("Results to text"). It will not make any difference to Results to grid

    Thanks Shiven:) If Answer is Helpful, Please Vote

    domingo, 5 de janeiro de 2020 23:07