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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Convert NULL to empty string - Conversion failed when converting from a character string to uniqueidentifier

Examples related to sql-server

Examples related to sql-server-2005

Examples related to uniqueidentifier