concatenating null with not null = null

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

SQL Server 2005 Forums

Transact-SQL (2005)

concatenating null with not null = null

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-11-30 : 02:25:57

select id,authorfirstname+' ' + authorlastname as name from authors order by authorlastname

if authorfistname is null it makes the whole string null - why?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-11-30 : 03:06:55

select id,COALESCE(authorfirstname,'')+' ' + authorlastname as name from authors order by authorlastname

Hope can help...but advise to wait pros with confirmation...

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-30 : 03:11:54

The same i have tried with my sample table, it displays only last name with a space before it.
I think it wont make the entire string null..

TABLE: b_staff
staffno fname lname
1 Mark Waugh
2 Adam Sinclaire
3 _ _ Gayle
4 _ _ Alexander
5 Will Smith

select staffno, fname + ' ' + lname as name
from b_staff order by lname;

Using this query i am getting all the rows..

Balaji.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 03:38:17

quote:
Originally posted by esthera

select id,authorfirstname+' ' + authorlastname as name from authors order by authorlastname

if authorfistname is null it makes the whole string null - why?

Becuause NULL appended with anyting is NULL

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-11-30 : 03:46:52

thanks :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 03:53:53

quote:
Originally posted by kbhere

The same i have tried with my sample table, it displays only last name with a space before it.
I think it wont make the entire string null..

TABLE: b_staff
staffno fname lname
1 Mark Waugh
2 Adam Sinclaire
3 _ _ Gayle
4 _ _ Alexander
5 Will Smith

select staffno, fname + ' ' + lname as name
from b_staff order by lname;

Using this query i am getting all the rows..

Balaji.K

It is because you dont have NULL in the table

Madhivanan

Failing to plan is Planning to fail

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-30 : 04:08:56

It is because you dont have NULL in the table

Madhivanan

Failing to plan is Planning to fail
[/quote]

Ya.. You are RIGHT!!..
That Column is having ' ' not NULL Value..
Thanks for your Information..

Balaji.K

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources