Convert INT column values to an empty string using ISNULL

Question

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
 FROM  #V;

this returns:

ID_Column
1
2
3
4
5
NULL
NULL

when I modify my CASE statement it as follows:

CASE WHEN CAST(ISNULL(ID,'') AS VARCHAR(10)) = '' THEN '' ELSE ID END AS  ID_Column

it returns:

ID_Column
1
2
3
4
5
0
0

Answer 1

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 0 or NULL.