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 2:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:48 PM
Points: 1,057, Visits: 3,127
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
Post #1471851
Posted Tuesday, July 9, 2013 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
Easy one, thanks for the question.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1471858
Posted Wednesday, July 10, 2013 12:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:10 AM
Points: 856, Visits: 163
Thanks .....
Post #1471968
Posted Wednesday, July 10, 2013 7:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
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.
Post #1472106
Posted Wednesday, July 10, 2013 7:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
Nice and interesting question..
Thanks..
Post #1472159
Posted Thursday, July 11, 2013 2:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:52 AM
Points: 3, Visits: 61
I did not know the difference until I type this SQL statement on SQL Management Studio :)
Post #1472777
Posted Thursday, July 11, 2013 11:58 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 6:31 AM
Points: 423, Visits: 535
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
my blog: http://db-berater.blogspot.de (german only!)
Post #1472863
Posted Monday, July 15, 2013 3:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:22 AM
Points: 271, Visits: 96
good question
Post #1473537
Posted Monday, July 22, 2013 3:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
Good one.
Post #1475903
Posted Thursday, July 25, 2013 10:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
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
Post #1477600
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse