在 SQL Server 中将空字符串 CAST/CONVERT 转换为 INT [英] CAST/CONVERT empty string to INT in SQL Server

问题描述

我在使用 CAST(Col1 AS INT) + CAST(Col2 AS INT) 时遇到了一个错误，其中 Col1 和 Col2 都是 VARCHAR，我得到了当 Col1 或 Col2 为空白时，有效结果出来了，我没想到会这样.我检查和 CAST(和 CONVERT)都有用 0 替换空白的默认行为:

SELECT CAST('' AS INT)选择转换(INT，'')

我查看了信息页面，但看不到任何信息参考解释为什么这是行为(或通过服务器设置更改它).我当然可以解决这个问题，但我想问一下为什么这是这种行为，因为我认为它不直观.

我实际上宁愿这个 CAST 失败或给出 NULL，是否有服务器设置会影响这个?

解决方案

考虑 SQL Server 中的 INT.它可以是三个值之一:

• 空
• 0
• 不是 0

因此，如果您正在转换/转换一个空字符串，您假设它是一个数字，那么 0 是最合乎逻辑的值.它允许区分 NULL 和 0.

SELECT CAST(NULL AS INT) -- NULLSELECT CAST('' AS INT) -- 0SELECT CAST('42' AS INT) -- 42

我认为这是合乎逻辑的.

如果你这样做了:

SELECT CAST('abc' AS INT)

你会得到:

<块引用>

将 varchar 值 'abc' 转换为数据类型 int 时转换失败.

如果您确实希望将空字符串作为 NULL 处理，请使用 NULLIF 作为 Bogdan 在他的回答中建议:

DECLARE @val VARCHAR(2) = ''SELECT CAST(NULLIF(@val,'') AS INT) -- 产生 NULL

<块引用>

NULLIF 如果两个表达式不相等，则返回第一个表达式.如果表达式相等，则 NULLIF 返回第一个表达式类型的空值.

最后，如果您的列存储的是 INT 值，则可以考虑将其数据类型更改为 INT.

I came across a bug where I was using CAST(Col1 AS INT) + CAST(Col2 AS INT) where both Col1 and Col2 are VARCHAR and I was getting valid results out when Col1 or Col2 was blank and I didn't expect this. I checked and CAST (and CONVERT) both have this default behavior of replacing blank with 0:

SELECT CAST('' AS INT)
SELECT CONVERT(INT, '')

I checked the info page and I can't see any reference to explain why this is the behavior (or change it through a server setting). I can of course work around this but I wanted to ask why this is the behavior as I do not think it is intuitive.

I'd actually rather this CAST failed or gave NULL, is there a server setting somewhere which effects this?

解决方案

Consider an INT in SQL Server. It can be one of three values:

• NULL
• 0
• Not 0

So if you're casting/converting an empty string, which you are assuming is a number, then 0 is the most logical value. It allows for a distinction between NULL and 0.

SELECT CAST(NULL AS INT) -- NULL
SELECT CAST('' AS INT)   -- 0
SELECT CAST('42' AS INT) -- 42

I'd say that's logical.

If you did:

SELECT CAST('abc' AS INT)

You'd get:

Conversion failed when converting the varchar value 'abc' to data type int.

If you do wish to handle empty strings as NULL use NULLIF as Bogdan suggests in his answer:

DECLARE @val VARCHAR(2) = ''

SELECT CAST(NULLIF(@val,'') AS INT)  -- produces NULL

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

Finally, if your columns are storing INT values, then consider changing its data type to INT if you can.

这篇关于在 SQL Server 中将空字符串 CAST/CONVERT 转换为 INT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！