I need to convert column ID of INT data type to a empty string ['']. I should not modify the source column data type, but need to convert it in my transformation in the other table. The ID column is "nullable" as it has null in it.This is my code.
CREATE TABLE #V(ID INT) ;
INSERT INTO #V
VALUES (1),(2),(3),(4),(5),(NULL),(NULL) ;
SELECT CASE WHEN CAST(ISNULL(ID,'') AS VARCHAR(10)) = '' THEN '' ELSE ID END AS ID_Column
CASE WHEN CAST(ISNULL(ID,'') AS VARCHAR(10)) = '' THEN '' ELSE ID END AS ID_Column
Is this what you want?
select coalesce(cast(id as varchar(255)), '') from #v;
You have to turn the entire result column into a single column. If you want a blank value, then the type is some sort of character string.
In your examples, the
else id means that the result from the
case is an integer, which is why you are getting either