33

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
Deeptechtons
  • 10,945
  • 27
  • 96
  • 178

4 Answers4

72
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)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • thank you for giving more than wanted. I learnt a lot more from your answer Will mark it as answered in 4 minutes Greetz – Deeptechtons Mar 29 '12 at 10:29
  • @Damien_The_Unbeliever 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. – theteague Aug 23 '17 at 16:04
  • Also tried ISNULL(CAST(ParentId as VARCHAR(50)),'') and COALESCE(CAST(ParentId as VARCHAR(50)),'') – theteague Aug 23 '17 at 16:08
  • @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) – Damien_The_Unbeliever Aug 23 '17 at 16:11
  • @Damien_The_Unbeliever 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. – theteague Aug 24 '17 at 17:14
9
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
Denis Agarev
  • 1,531
  • 4
  • 17
  • 34
Sudipto
  • 91
  • 1
  • 1
8

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
Curtis
  • 101,612
  • 66
  • 270
  • 352
0

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.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291