SQL Server 2012 Concat

Question

Post reply

SQL Server 2012 Concat

psingla

Hall of Fame

Points: 3840
More actions
July 8, 2013 at 9:40 pm

#295400

Comments posted to this topic are about the item SQL Server 2012 Concat
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Nice and easy! Thanks Pramod

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Lokesh Vij (7/8/2013)
Nice and easy! Thanks Pramod

+1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 3

Quite easy, Really very good question. Still 36% people are wrong. It shows important of this question.

Keep it up man !!! 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 4

select concat(null + 'Hello','testString') as a

, null+'testString' as b

It simply returns 'testString', NULL

😀

psingla Hall of Fame Points: 3840 More actions · Answer 5

Carlo Romagnano (7/9/2013)
select concat(null + 'Hello','testString') as a
, null+'testString' as b
It simply returns 'testString', NULL
😀

Looks ok to me as :

null + 'Hello' --->NULL

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 6

Some formatting with CONCAT:

SELECT

concat

(

'Name: ',t.name

,CHAR(9) + 'Last name: ',t.lastname

,CHAR(9) + 'Total: ',SUM(e.Score)

) AS csv_line

FROM Students t

JOIN Exams e

ON t.ID = e.ID_Student

GROUP BY t.name

,t.lastname

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

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');

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 8

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!

TomThomson SSC Guru Points: 104773 More actions · Answer 9

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.

Tom

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 10

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!

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 11

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 :crazy:

mnmanikantan Old Hand Points: 349 More actions · Answer 12

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.

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 13

Thanks nice and easy, I knew this one from experience.:blush:

Dana Medley SSCertifiable Points: 6764 More actions · Answer 14

Dana Medley

SSCertifiable

Points: 6764

July 9, 2013 at 7:22 am

#1630784

Thanks for the question!

Everything is awesome!