Concatinating of Strings

Posted by Srikrishna777 under Sql Server on 11/30/2009 | Views : 1194 | Status : [Member] | Replies : 2
Hi,

In my table,I have FirstName,MiddleName,LastName columns and I want to display the total name with column heading Name.
When I am doing this,If any of the value is NULL,I am not getting the required output.Instead,I am getting NULL values.How to handle it.?

My query looks like this,

Select t.FirstName+' '+t.MiddleName+' '+t.LastName Name from Client as t

Please help me.

Thanks,
Srikrishna




Responses

Posted by: Pandians on: 11/30/2009 [Member] [MVP] Silver

Up
0
Down
Hi
You have two options

Option : 1
Select ISNULL(t.FirstName,'') + ' ' + ISNULL(t.MiddleName,'') + ' ' + ISNULL(t.LastName,'') [Name] from Client as t

Option : 2
SET CONCAT_NULL_YIELDS_NULL OFF
Select t.FirstName + ' ' + t.MiddleName + ' ' + t.LastName [Name] from Client as t
SET CONCAT_NULL_YIELDS_NULL ON

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Srikrishna777, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Srikrishna777 on: 12/1/2009 [Member] Starter

Up
0
Down
Thanks Pandians.
I have followed the Option1 .It has solved the issue.
Thankyou very much.


Srikrishna777, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response