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 1234»»»

CONCAT 1 Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 9:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Comments posted to this topic are about the item CONCAT 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1487028
Posted Wednesday, August 21, 2013 11:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
If you already use CONCAT function then it's easy to you. Otherwise first you need to check about CONCAT function.
I love this function. It removes some overhead while adding multiple string.
Previously, we need to place an ISNULL for each and every string while adding.

Thanks Ron




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1487058
Posted Thursday, August 22, 2013 12:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:18 AM
Points: 1,057, Visits: 2,359
Good One. Thank you for posting.

(well... this qtod very similar to example given under CONCAT function in BOL...)


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1487070
Posted Thursday, August 22, 2013 12:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:44 AM
Points: 1,392, Visits: 516
BOL: However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL for the current session.
The right answer is: it depends.
Post #1487077
Posted Thursday, August 22, 2013 2:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
2 points for this easy question?

Thanks Ron, always nice to have SQL 2012 questions.




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 #1487106
Posted Thursday, August 22, 2013 3:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 PM
Points: 6,133, Visits: 8,398
palotaiarpad (8/22/2013)
BOL: However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL for the current session.
The right answer is: it depends.

Maybe you should specify WHERE in Books Online you found that quote. I am pretty sure that it's on a page that does not describe the CONCAT() function, but other methods of string concatenation. CONCAT() is not affected by this setting.


That being said, it *IS* possible to get this query to error out. If the collation of the database that is active when running this code is different from the default server collation (and hence the collation of tempdb), you will get a collation conflict because the age will be converted to a string in the database collation, and the other arguments are all in the tempdb collation.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1487146
Posted Thursday, August 22, 2013 4:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:22 AM
Points: 1,830, Visits: 6,646
Danny Ocean (8/21/2013)
I love this function. It removes some overhead while adding multiple string.


And I hate it for exactly the same reason!

At last they've deprecated CONCAT_NULL_YIELDS_NULL but then spoil it by providing a new method that encourages the same sloppy coding and the same misunderstandings of how NULL works
Post #1487147
Posted Thursday, August 22, 2013 6:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 2:16 PM
Points: 157, Visits: 169
I don't agree that it encourages sloppy coding. You need to know how a language works and be a decent coder to not be sloppy. I don't see how providing more user-friendly functions contributes to sloppy coding. It makes non-sloppy coders' lives easier.

This is much easier/quicker than coalesce or:

ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName + ' ', '') + CONVERT(VARCHAR(3), Age)

I'm definitely happy I no longer have to explicitly convert int to string.
Post #1487203
Posted Thursday, August 22, 2013 7:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:49 AM
Points: 2,620, Visits: 2,467
Nick Doyle (8/22/2013)
I don't agree that it encourages sloppy coding. You need to know how a language works and be a decent coder to not be sloppy. I don't see how providing more user-friendly functions contributes to sloppy coding. It makes non-sloppy coders' lives easier.

This is much easier/quicker than coalesce or:

ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName + ' ', '') + CONVERT(VARCHAR(3), Age)

I'm definitely happy I no longer have to explicitly convert int to string.

+1
In this case CONCAT also is faster than ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName + ' ', '') + ISNULL(CONVERT(VARCHAR(3), Age),'')
Because you call ONE function and 4 parameters vs 4 functions with 8 parameters and ONE CAST.
Post #1487232
Posted Thursday, August 22, 2013 7:14 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 4,611, Visits: 4,067
This was a nice one for me because we don't have 2012 (sigh) and it got me to research a 2012-only function. Thanks.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1487238
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse