Select column, if blank select another column else ' ' if BOTH are null-CodePudding

Using SQL Server, I would like to have a field print out as blank rather than 'null'. The query is to first pick up phone number from one table - if null then it picks up from another table. If both are null, then I would like to have it return ' ' (blank) rather than 'null'.

I have tried multiple things in the 2nd line but still get nulls instead of blanks to print.

SELECT case when dbo.vwPersonDistinctPhone.phone IS NULL THEN PERSONAL_PHONE_NUMBER 

when (dbo.vwPersonDistinctPhone.phone is null and PERSONAL_PHONE_NUMBER is null)  then ' '

else dbo.vwPersonDistinctPhone.phone END AS 'phone',

CodePudding user response：

i dont think you need the second when, you should be able to just to do this, there is a isnull function in sql server that if the value is null then it replaces it with the second parameter. So in this case if personal_phone_number is null then it will be '' or personal_phone_number if is not.

SELECT case when dbo.vwPersonDistinctPhone.phone IS NULL THEN ISNULL(PERSONAL_PHONE_NUMBER, '') else dbo.vwPersonDistinctPhone.phone END AS 'phone',

CodePudding user response：

You need to swap the case statements as your second case statement will not be executed if dbo.vwPersonDistinctPhone.phone is null is true.

SELECT when (dbo.vwPersonDistinctPhone.phone is null and PERSONAL_PHONE_NUMBER is null) then ' ' 
case when dbo.vwPersonDistinctPhone.phone IS NULL THEN PERSONAL_PHONE_NUMBER
else dbo.vwPersonDistinctPhone.phone END AS 'phone',