SQL Server 2012 Concat

  • psingla

    Hall of Fame

    Points: 3840

    Comments posted to this topic are about the item SQL Server 2012 Concat

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Lokesh Vij

    SSChampion

    Points: 10836

    Nice and easy! Thanks Pramod

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kapil_kk

    SSC-Insane

    Points: 21316

    Lokesh Vij (7/8/2013)


    Nice and easy! Thanks Pramod

    +1 πŸ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Quite easy, Really very good question. Still 36% people are wrong. It shows important of this question.

    Keep it up man !!! πŸ™‚

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    select concat(null + 'Hello','testString') as a

    , null+'testString' as b

    It simply returns 'testString', NULL

    πŸ˜€

  • psingla

    Hall of Fame

    Points: 3840

    Carlo Romagnano (7/9/2013)


    select concat(null + 'Hello','testString') as a

    , null+'testString' as b

    It simply returns 'testString', NULL

    πŸ˜€

    Looks ok to me as :

    null + 'Hello' --->NULL

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    Some formatting with CONCAT:

    SELECT

    concat

    (

    'Name: ',t.name

    ,CHAR(9) + 'Last name: ',t.lastname

    ,CHAR(9) + 'Total: ',SUM(e.Score)

    ) AS csv_line

    FROM Students t

    JOIN Exams e

    ON t.ID = e.ID_Student

    GROUP BY t.name

    ,t.lastname

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Carlo Romagnano (7/9/2013)


    select concat(null + 'Hello','testString') as a

    , null+'testString' as b

    It simply returns 'testString', NULL

    πŸ˜€

    Yup. If you wanted to get 'HellotestString', you'd use SELECT CONCAT(NULL, 'Hello', 'testString');


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    Hugo Kornelis (7/9/2013)


    Carlo Romagnano (7/9/2013)


    select concat(null + 'Hello','testString') as a

    , null+'testString' as b

    It simply returns 'testString', NULL

    πŸ˜€

    Yup. If you wanted to get 'HellotestString', you'd use SELECT CONCAT(NULL, 'Hello', 'testString');

    No, I didn't want!

    It's a joke!

  • TomThomson

    SSC Guru

    Points: 104773

    Nice straightforward question. Good to see a question about another new feature in SQL 2012.

    However, introducing a weird ODBC function into T-SQL seems to me a bad idea. It's treatment of NULL is a mechanism to allow people to continue to misuse null once the ability to switch ANSI nulls off is removed, and continue to write obscure buggy code instead of cleaning things up.

    I guess the ability to write (for example) "concat(5,6,7,8)" and get 5678 might be considered useful, because it saves a bit of typing - not in the case of literals since "'5'+'6'+'7'+'8'" is 15 characters and so is the expression with concat - but when the arguments are column names "concat(c1,c2,c3,c4)" is quite a lot shorter than "cast(c1 as varchar)+cast(c2 as varchar)+cast(c3 as varchar)+cast(c4 as varchar)" but I don't believe the benefits of being able to do that (of similar things with float) outweigh the scope for misuse of this form of null handling, so on balance I feel concat is something I would rather live without.

    Tom

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    L' Eomot InversΓ© (7/9/2013)


    Nice straightforward question. Good to see a question about another new feature in SQL 2012.

    However, introducing a weird ODBC function into T-SQL seems to me a bad idea. It's treatment of NULL is a mechanism to allow people to continue to misuse null once the ability to switch ANSI nulls off is removed, and continue to write obscure buggy code instead of cleaning things up.

    I guess the ability to write (for example) "concat(5,6,7,8)" and get 5678 might be considered useful, because it saves a bit of typing - not in the case of literals since "'5'+'6'+'7'+'8'" is 15 characters and so is the expression with concat - but when the arguments are column names "concat(c1,c2,c3,c4)" is quite a lot shorter than "cast(c1 as varchar)+cast(c2 as varchar)+cast(c3 as varchar)+cast(c4 as varchar)" but I don't believe the benefits of being able to do that (of similar things with float) outweigh the scope for misuse of this form of null handling, so on balance I feel concat is something I would rather live without.

    CONCAT makes life easy!

  • danielfountain

    SSCarpal Tunnel

    Points: 4229

    Carlo Romagnano (7/9/2013)


    Hugo Kornelis (7/9/2013)


    Carlo Romagnano (7/9/2013)


    select concat(null + 'Hello','testString') as a

    , null+'testString' as b

    It simply returns 'testString', NULL

    πŸ˜€

    Yup. If you wanted to get 'HellotestString', you'd use SELECT CONCAT(NULL, 'Hello', 'testString');

    No, I didn't want!

    It's a joke!

    Not sure i get it :crazy:

  • mnmanikantan

    Old Hand

    Points: 349

    Please note that the behavior of adding something to NULL depends on the option 'CONCAT NULL YEILDS NULL'. Please see the below msdn link for further details.

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

    This will set by default and hence adding NULL to a string yields NULL. In future versions of sql server Microsoft claims to get rid of this feature and always keep the setting on.

  • Mike Is Here

    Hall of Fame

    Points: 3348

    Thanks nice and easy, I knew this one from experience.:blush:

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thanks for the question!



    Everything is awesome!

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply