Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server 2012 Concat Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 3:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:57 AM
Points: 2,587, Visits: 2,443
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!
Post #1471511
Posted Tuesday, July 9, 2013 4:30 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, November 7, 2014 7:31 AM
Points: 769, Visits: 854
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
Post #1471526
Posted Tuesday, July 9, 2013 5:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 5:01 AM
Points: 32, Visits: 84
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.
Post #1471563
Posted Tuesday, July 9, 2013 6:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:41 AM
Points: 1,393, Visits: 485
Thanks nice and easy, I knew this one from experience.
Post #1471581
Posted Tuesday, July 9, 2013 7:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:23 PM
Points: 1,951, Visits: 1,473
Thanks for the question!



Everything is awesome!
Post #1471605
Posted Tuesday, July 9, 2013 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 10:10 AM
Points: 12, Visits: 114
When I run it says 'concat' is not a recognized built0in function name.
Post #1471611
Posted Tuesday, July 9, 2013 7:34 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:50 AM
Points: 608, Visits: 1,052
its new function added in SQL 2012

Pramod
SQL Server DBA | MCSA SQL Server 2012

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Post #1471615
Posted Tuesday, July 9, 2013 7:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 10:10 AM
Points: 12, Visits: 114
Thanks
Post #1471618
Posted Tuesday, July 9, 2013 8:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
Nice and simple. Thanks, Pramod!
Post #1471673
Posted Tuesday, July 9, 2013 9:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
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
Post #1471686
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse