使用 ISNULL 将 INT 列值转换为空字符串 [英] Convert INT column values to an empty string using ISNULL

问题描述

我需要将 INT 数据类型的列 ID 转换为空字符串 [''].我不应该修改源列数据类型，而是需要在我在另一个表中的转换中转换它.ID 列是可为空的"，因为其中包含空值.这是我的代码.

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;

返回:

ID_Column
1
2
3
4
5
NULL
NULL

当我修改我的 CASE 语句时，它如下:

when I modify my CASE statement it as follows:

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

它返回:

ID_Column
1
2
3
4
5
0
0

推荐答案

这是您想要的吗?

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.

在您的示例中，else id 表示 case 的结果是一个整数，这就是为什么您得到 0或 NULL.

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.

这篇关于使用 ISNULL 将 INT 列值转换为空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！