SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2012 Concat


SQL Server 2012 Concat

Author
Message
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7650 Visits: 3401
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!

I run on tuttopodismo
Daniel Fountain
Daniel Fountain
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1425 Visits: 890
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
:-D

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
mnmanikantan
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 110
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
Mike Is Here
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 513
Thanks nice and easy, I knew this one from experience.Blush
Dana Medley
Dana Medley
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3162 Visits: 1707
Thanks for the question!



Everything is awesome!
David Stout
David Stout
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 135
When I run it says 'concat' is not a recognized built0in function name.
psingla
psingla
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 1249
its new function added in SQL 2012

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
David Stout
David Stout
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 135
Thanks
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12773 Visits: 5010
Nice and simple. Thanks, Pramod!
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2788 Visits: 2582
Carlo Romagnano (7/9/2013)
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!


While I can certainly see the advantages of replacing NULL arguments with empty strings, as does the CONCAT function, I also see Tom's point. Improper handling of NULLs already causes enough trouble, and it just seems like a function that doesn't require a developer to think consistently about the issue may encourage bad habits. Those bad habits could be especially troublesome when MS finally deprecates the ANSI_NULLS OFF and CONCAT_NULL_YIELDS_NULL OFF settings. MS is reintroducing behavior in the CONCAT function that it is eliminating in all other contexts by deprecating the CONCAT_NULL_YIELDS_NULL OFF setting.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search