SQL Server 2012 Concat

  • 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/

  • 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

  • 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/

  • 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 !!!

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

    , null+'testString' as b

    It simply returns 'testString', NULL

    πŸ˜€

  • 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/

  • 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

  • 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/

  • 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!

  • 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

  • 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!

  • 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:

  • 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.

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

  • Thanks for the question!



    Everything is awesome!

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

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