| Author |
Topic |
|
gamolchan
Starting Member
8 Posts |
 Posted - 2006-10-05 : 16:50:25
|
| When I use the t-sql code:SELECT (au_lname + ', ' + au_fname) AS NameIf au_fname is null, then Name is null even if au_lname is not nullI am confused, but that is not new! :-)TIA for any help |
|
|
gamolchan
Starting Member
8 Posts |
Posted - 2006-10-05 : 16:54:32
|
| Well I lied, when I use the example posted, I get results, but when I change the code to reflect the field names in my live DB, if the Fname is null, then Name is null? So I am still confused :-) |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-05 : 17:02:55
|
| That is what it is supposed to do.When you concatenate strings, and any part is NULL, then the result will be NULL.CODO ERGO SUM |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-05 : 17:48:50
|
Use the ISNULL function to return a value in place of the NULL, so for example ISNULL(au_fname, '') will return the value of au_lname if it is not null, or '' if it is null. So now you can do this SELECT (au_lname + ', ' + ISNULL(au_fname, '')) AS Name The problem is now, although the result will not be null, it will have a comma after the last name when there is no first name, which you don't really want, so then you should use a CASE like thisSELECT CASE WHEN au_fname IS NULL THEN au_lname ELSE au_lname + ', ' + au_fname END AS Name That will not use the expression at all when au_fname is null so that you don't get the unnecessary comma. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-05 : 17:56:12
|
quote:
I am confused
Just to explain further - while it may seem strange that SQL Server would make the result null when you concatenate null with a value, it actually makes very good sense. You might expect that it would return the au_lname and the comma when the au_fname is null, but that would require SQL Server to assume that null means a blank string.SQL Server doesn't make assumptions about the meaning of null - ever. Is null really a blank string or zero or 1/1/1900 or whatever? No, it is unknown, therefore you have to really see your example as what is au_lname concatenated with a comma concatenated with unknown, and clearly the result must be unknown.The example I use is this - if we had an employee table with a salary column and we were giving everyone a 10% raise. If your current salary was NULL, would you want SQL Server to give the result for (NULL plus 10%) as NULL or 0 (assuming null in a numeric column seems reasonable if we are assuming null in a character column is a blank string)?! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-05 : 21:11:23
|
| Note that NULL is not equal to ''MadhivananFailing to plan is Planning to fail |
|
|
|
gamolchan
Starting Member
8 Posts |
Posted - 2006-10-06 : 09:15:50
|
| Thanks for the info. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
gamolchan
Starting Member
8 Posts |
Posted - 2006-10-06 : 10:44:30
|
| Thanks for all the help, and I got it to work. I think when I tested the tsql on the pubs DB, I created blanck Lname records and not null Lname records. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-10-06 : 11:23:17
|
My favorite syntax for covering all bases:SELECT coalesce(au_lname + ', ' + au_fname, au_lname, au_fname) AS Name "Once you establish possibility, all that remains are desire and determination." |
|
|
|
|
string concatenation