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

Gordon Linoff · Accepted Answer · 2016-10-08 20:30:35Z

7

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.

answered Oct 8, 2016 at 20:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Data Engineer Over a year ago

Great! Works perfect.

Collectives™ on Stack Overflow

Convert INT column values to an empty string using ISNULL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related