How to Merge columns with null value data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Merge columns with null value data

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-25 : 15:13:05

I have a tabl1 with Id,Fname,MName,LName fields.
I want to create temp table #Temp1 with ID and (FName + MName +LName) FullName

I tried this:

declare @tbl1 table
(ID INT,
FName varchar(20),
MName varchar(3),
LName varchar(20)
)

INSERT INTO @tbl1
SELECT 1, 'Abc',NULL,'xyz'

INSERT INTO @tbl1
SELECT 2, 'xxx','b','mm'

INSERT INTO @tbl1
SELECT 3, 'nnn',NULL,NULL

INSERT INTO @tbl1
SELECT 4, 'ppp','iii',NULL

select ID,FName+';'+MName+';'+LName as FullName into #Temp from @tbl1
select * from #Temp

drop table #Temp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-25 : 15:26:19

Give the expression a name.

select ID,FName +';'+ MName +';'+ LName as FullName INTO #Temp1 FROM tbl1

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-25 : 15:30:00

I tried but the problem is NULL value. I can not get the data if Mname is null or Lname is null but i have data on FName

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 15:54:28

Use COALESCE...

select ID,COALESCE(FName,'') +';'+ COALESCE(MName,'') +';'+ COALESCE(LName,'') as FullName INTO #Temp1 FROM tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-25 : 15:57:50

thanks vijayisonly,

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 16:01:53

Ok...gotcha...how about this?

select COALESCE(FName,'') + ';' + COALESCE(MName + ';','') + COALESCE(LName,'')
as FullName INTO #Temp1 FROM tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-25 : 16:03:41

thanks perfect.

quote:
Originally posted by vijayisonly

Ok...gotcha...how about this?

select COALESCE(FName,'') + ';' + COALESCE(MName + ';','') + COALESCE(LName,'')
as FullName INTO #Temp1 FROM tbl1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 16:04:53

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-25 : 16:14:11

Run this:

SELECT 1, 'Abc '+ NULL + ' xyz'
SELECT 2, 'Abc '+ 'qrs' + ' xyz'

Then this:
SET concat_null_yields_null OFF
SELECT 1, 'Abc '+ NULL + ' xyz'
SELECT 2, 'Abc '+ 'qrs' + ' xyz'
SET concat_null_yields_null ON

The set concat_null_yields OFF will allow you to concatenate null values without nulling the entire string.

-Edit - This is SQL 2000. I'm guessing it applies to 2005.

Terry

-- Procrastinate now!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-25 : 17:32:57

The SET option works well but you need to set it at the connection level.. for example if you have a proc you need to set it at the beginning and need to remember to close it at the end.. otherwise you might get unexpected results for other queries.. also these SET options can cause query plan recompiles..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources