enigma6205 enigma6205 - 2 months ago 14
SQL Question

Convert INT column values to an empty string using ISNULL

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

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.