Convert NULL to empty string - Conversion failed when converting from a character string to uniqueidentifier

Question

Using SQL Server 2005 how do I get the below statement or rather the output as i want it to be.

SELECT Id   'PatientId',
       ISNULL(ParentId,'')  'ParentId'
FROM Patients

ParenId is a uniqueidentifier that allows NULL, but seems that query optimizer tries to also convert '' back to uniqueidentifier for the rows where ParentId = NULL.As the title says that's the exact error info the query runner throws at my face!!

• How do i get the server to return empty string for ParentId = NULL

Allbee

Answer 1

SELECT Id   'PatientId',
       ISNULL(CONVERT(varchar(50),ParentId),'')  'ParentId'
FROM Patients

ISNULL always tries to return a result that has the same data type as the type of its first argument. So, if you want the result to be a string (varchar), you'd best make sure that's the type of the first argument.

---

COALESCE is usually a better function to use than ISNULL, since it considers all argument data types and applies appropriate precedence rules to determine the final resulting data type. Unfortunately, in this case, uniqueidentifier has higher precedence than varchar, so that doesn't help.

(It's also generally preferred because it extends to more than two arguments)

Crinoid

thank you for giving more than wanted. I learnt a lot more from your answer Will mark it as answered in 4 minutes Greetz – Allbee

@Crinoid I tried 'COALESCE(CONVERT(varchar(50),ParentId),'')' and 'ISSNULL(CONVERT(varchar(50),ParentId),'')' and I'm still getting a zero instead of an empty cell. My ParentId is smallint. – Girard

Also tried ISNULL(CAST(ParentId as VARCHAR(50)),'') and COALESCE(CAST(ParentId as VARCHAR(50)),'') – Girard

@bteague - COALESCE and ISNULL are for dealing with nulls. Not zeroes. If you want a zero to become an empty string, you're probably looking for a CASE expression. Bear in mind though that if you want one outcome to be the empty string, you need to ensure that all possible results from the CASE are also strings. (Since e.g. an int can't contain an empty string) – Crinoid

@Crinoid My datapoint was smallint, when i tried to convert the null to an empty string I was getting a zero and I did not want zero's. ISNULL(CONVERT(varchar(50),ParentID),'') was correct. My query was a series of 3 queries joined by union all and I only converted this field in the first block of code. The end result was zero's. Since the format of the first block of code typically carries (i thought) the format through the rest of that column in the query I expected it would have thrown an error rather than ignoring my varchar conversion. Thank you for your time. – Girard

Answer 2

Select ID, IsNull(Cast(ParentID as varchar(max)),'') from Patients

This is needed because field ParentID is not varchar/nvarchar type. This will do the trick:

Select ID, IsNull(ParentID,'') from Patients

Overcome

Answer 3

You need to CAST the ParentId as an nvarchar, so that the output is always the same data type.

SELECT Id   'PatientId',
       ISNULL(CAST(ParentId as nvarchar(100)),'')  'ParentId'
FROM Patients

Odie

Answer 4

Starting with Sql Server 2012: string concatenation function CONCAT converts to string implicitly. Therefore, another option is

SELECT Id AS 'PatientId',
       CONCAT(ParentId,'') AS 'ParentId'
FROM Patients

CONCAT converts null values to empty strings.

Some will consider this hacky, because it merely exploits a side effect of a function while the function itself isn't required for the task in hand.

Softball