SQL Server 2012 Concat

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

mickyT SSChampion Points: 10360 More actions · Answer 6

wolfkillj (7/9/2013)
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.

While this function is mistreating NULLs in the pure sense, I am going to be grateful for being to replace some long and complicated to read expressions that include CASTs, ISNULLs and occasionally NULLIFs.

I suppose it comes down to if the NULL is being used as an unknown or as a no value.

We could look at the function slightly differently and rather than saying it treats NULLs as empty strings, say that it ignores NULLs while concatenating:-) similar to the aggregate functions

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Easy one, thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Bangla Hall of Fame Points: 3137 More actions · Answer 8

Bangla

Hall of Fame

Points: 3137

July 10, 2013 at 12:44 am

#1631081

Thanks .....

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 9

Nice question. On of the first editions of the .NET data libraries had NULL value operator and could not read or assign NULL to a value.

IMHO - NULL values are useless and concat helps us reduce the amount of ISNULL code needed when having to parse together names when the Middle name may be NULL value 45% of the time, a non breaking space 10%, a '' or empty string.

Thanks M$ for adding something that acts more like & and less like the add function.

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 10

PChiragS

SSCarpal Tunnel

Points: 4965

July 10, 2013 at 7:58 am

#1631239

Nice and interesting question..

Thanks..

FrugalDeveloper SSC Enthusiast Points: 169 More actions · Answer 11

I did not know the difference until I type this SQL statement on SQL Management Studio 🙂

“Let your ‘Yes’ mean ‘Yes,’ and your ‘No’ mean ‘No.’ Anything more is from the evil one.” (Matthew 5:37)

Uwe Ricken Hall of Fame Points: 3142 More actions · Answer 12

The answer isn't correct because "it depends"!

I answered it correct but only because of the standard settings for the session parameter [CONCAT_NULL_YIELDS_NULL].

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

SELECT session_id, concat_null_yields_null FROM sys.dm_exec_sessions WHERE session_id = @@spid;

-- now it is the default behaviour and the expected answer of QotD

select concat(null,'testString') as a

, null+'testString' as b

SET CONCAT_NULL_YIELDS_NULL ON;

GO

SELECT session_id, concat_null_yields_null FROM sys.dm_exec_sessions WHERE session_id = @@spid;

-- now both concatenations will return the same result!

select concat(null,'testString') as a

, null+'testString' as b

As others said - good question because of the amount of failed answers. But this - little - issue is worth to be mentioned 🙂

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

udayroy15 Ten Centuries Points: 1076 More actions · Answer 13

udayroy15

Ten Centuries

Points: 1076

July 15, 2013 at 3:06 am

#1632363

good question 🙂

sqlnaive SSCoach Points: 17437 More actions · Answer 14

sqlnaive

SSCoach

Points: 17437

July 22, 2013 at 3:14 am

#1634374

Good one. 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

Nice N EZ

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events