sql-server - with - sql update null to empty string
Convert NULL to empty string-Conversion failed when converting from a character string to uniqueidentifier (2)
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
You need to
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
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)