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
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
.