Concat two fields in SQL server when one column is null

[sirnose1]

I have table that has columns FName, Spouse and LName. If one of the columns (Spouse) is null, I get no results for that column. How do you write the query to show the rows when the spouse column is null and just show the first and last name for that column?

SELECT resFName + ' and ' + resSpouse + ' ' + resLName AS Resident, address, email, residentID
FROM dbo.tblResidents

 

[AlexCuse]

Like this perhaps? COALESCE will replace a null value with whatever you tell it to (provided data types are the same).

You can also use CASE to get rid of the 'and' when resSpouse is null.

SELECT resFName 
+ case when resSpouse is null then '' else ' and ' end
+ COALESCE(resSpouse, '') 
+ ' ' 
+ resLName AS Resident
, address
, email
, residentID
FROM   dbo.tblResidents

I suggest you read about COALESCE and CASE in Books online (SQL Server Help) as they are very important functions to understand.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sirnose1]

Thanks. That has was a big help. Where is the link for "Books online SQL Server Help?" I looked through my books and cant find anything on COALESCE.

 

[SQLBill]

Go to Start>Programs>Microsoft SQL Server>Books OnLine.

With 2005, it's under Documentation and Tutorials.

-SQLBill

Posting advice: FAQ481-4875

 

[Qik3Coder]

Try this:

SELECT 
ISNULL(resFName,'') + 
Case when 
 ISNULL(resFName,'') <> '' 
 AND ISNULL(resSpouse,'') <> '' 
   THEN ' and ' 
   ELSE '' END + 
ISNULL(resSpouse,'') + ' ' + resLName AS Resident, address, email, residentID
FROM dbo.tblResidents

-The answer to your problem may not be the answer to your question.

 

[gmmastros]

You can also set a config value to solve this problem.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Col1 [COLOR=blue]varchar[/color](10), Col2 [COLOR=blue]VarChar[/color](10), Col3 [COLOR=blue]VarChar[/color](10))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a'[/color], [COLOR=red]'b'[/color], [COLOR=red]'c'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](Null, [COLOR=red]'b'[/color], [COLOR=red]'c'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](Null, Null, [COLOR=red]'c'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](Null, Null, Null)

[COLOR=blue]Select[/color] Col1 + Col2 + Col3
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Set[/color] CONCAT_NULL_YIELDS_NULL [COLOR=blue]OFF[/color]

[COLOR=blue]Select[/color] Col1 + Col2 + Col3
[COLOR=blue]From[/color]   @Temp

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

George,

You're right that setting this value can work. But I'd like to add that it's probably bad practice to set this value at a database level. Someone, some day, is going to get their queries royally screwed up if you design a whole database around concatenating nulls behaving unusually.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[gmmastros]

That's why I use disconnected recordsets and disconnect from the database after every call.

The SET statement only affects the current session. The next time you connect, that setting is lost.

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

I was referring to the equivalent database-level option:

sp_dboption 'yourdatabase', 'concat null yields null', 'false'

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]