August 21, 2013 at 11:29 pm
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 !!!
August 22, 2013 at 12:14 am
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.
August 22, 2013 at 12:49 am
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. 😀
August 22, 2013 at 2:13 am
2 points for this easy question? :w00t:
Thanks Ron, always nice to have SQL 2012 questions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2013 at 3:55 am
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.
August 22, 2013 at 4:00 am
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 :crazy:
August 22, 2013 at 6:24 am
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. 😀
August 22, 2013 at 7:06 am
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.
August 22, 2013 at 7:14 am
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.
August 22, 2013 at 7:36 am
Carlo Romagnano (8/22/2013)
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.
That's why i love it. :-):-):-)
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
August 22, 2013 at 8:12 am
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. 😀
Having a concat function which requires string arguments so that explicit conversion is not needed in the cases where implicit conversion is possible is indeed a nice feature.
However, if you want a to write code in accordance with the relational model, you will also want to ensure that NULL can not be interpreted as zero or as a zero length string or as anything else other than "this data is not available, the value is unknown" and it is, as Toreador says, sloppy coding to use NULL for anything else; if there is some data for which absence should be interpreted as a zero length string, then declare it as such: in such a case the table should be declared as
CREATE TABLE #temp (
emp_name nvarchar(20) NOT NULL DEFAULT(''),
emp_middlename nvarchar(20) NOT NULL DEFAULT(''),
emp_lastname nvarchar(20) NOT NULL DEFAULT(''),
age int NOT NULL);
This avoids the misuse of NULL to represent a known value. The introduction of this concat function with its stupid treatment of NULL is just a sop to those who were horrified that SQL Server is about to switch to standard treatment of NULL and either were incapable of thinking straight about how to upgrade their sloppily coded legacy software to cope or too set in their ways and wanted to continue misusing NULL in a sloppy and error prone manner for new development. I firmly believe that MS should not have thrown them that sop, we would have suffered less from buggy software in future if they hadn't.
edit: I forgot to say "good question, Ron".
Tom
August 22, 2013 at 8:56 am
L' Eomot Inversé (8/22/2013)
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. 😀
Having a concat function which requires string arguments so that explicit conversion is not needed in the cases where implicit conversion is possible is indeed a nice feature.
However, if you want a to write code in accordance with the relational model, you will also want to ensure that NULL can not be interpreted as zero or as a zero length string or as anything else other than "this data is not available, the value is unknown" and it is, as Toreador says, sloppy coding to use NULL for anything else; if there is some data for which absence should be interpreted as a zero length string, then declare it as such: in such a case the table should be declared as
CREATE TABLE #temp (emp_name nvarchar(20) NOT NULL DEFAULT(''),
emp_middlename nvarchar(20) NOT NULL DEFAULT(''),
emp_lastname nvarchar(20) NOT NULL DEFAULT(''),
age int NOT NULL);
This avoids the misuse of NULL to represent a known value. The introduction of this concat function with its stupid treatment of NULL is just a sop to those who were horrified that SQL Server is about to switch to standard treatment of NULL and either were incapable of thinking straight about how to upgrade their sloppily coded legacy software to cope or too set in their ways and wanted to continue misusing NULL in a sloppy and error prone manner for new development. I firmly believe that MS should not have thrown them that sop, we would have suffered less from buggy software in future if they hadn't.
edit: I forgot to say "good question, Ron".
+1
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
August 22, 2013 at 9:07 am
Hugo Kornelis (8/22/2013)
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.
:blush:
You are right!
Wrong page found. It was the concat operator: + 😀
August 22, 2013 at 10:26 am
Koen Verbeeck (8/22/2013)
2 points for this easy question? :w00t:Thanks Ron, always nice to have SQL 2012 questions.
Wow missed that... it's 2 points?
Good question, thanks Ron!
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply