sql server 2008 return value getting shortened when using ISNULL and NULLIF

Question

I have this select statement where I check whether phone number in null or empty and if they are then I would return 'No Phone Number is available'. Like this

SELECT 
      Name, 
      ISNULL(NULLIF(Phone, ''), 'No Phone Number is available') AS Phone
FROM Person

But when the phone number is null or empty I am not getting the full text 'No phone number is available'. Only the first 20 characters is getting returned. The length of Phone field is also 20. So I think this is returning text depending on the length of the phone field.

Is there a way to correct this without changing the field length?

Answer 1

You are correct.

ISNULL uses the datatype and length of the first parameter. COALESCE takes the "highest precedence" one. So:

COALESCE(NULLIF(Phone, ''), 'No Phone Number is available') AS Phone

Or

ISNULL(NULLIF(CAST(Phone as varchar(30)), ''), 'No Phone Number is available') AS Phone