SQL Server：将varchar转换为INT

Question

我遇到了一个问题，需要将一个 varchar 列 UserID 转换为 INT 类型。虽然这个 UserID 列最初应该就是 INT 类型，但由于一些复杂的原因它被创建成了 varchar 类型。

我尝试了一下方法，但是它不起作用并且给出了一个错误：

select CAST(userID AS int) from audit

错误：

在将 varchar 值 '1581............................................................................................................................' 转换为数据类型 int 时，转换失败。

我尝试使用 select len(userID) from audit 查询了一下，返回了一个长度为128个非空格字符的字符串。

我也试图检测ID号后面是否有ASCII字符，并且他们的值为0。

我已经试过了 LTRIM, RTRIM, 和将 char(0) 替换为 '' , 但都不起作用。

唯一有效的方法就是像下面这样固定字符数目，但是 UserID 并不总是4个字符。

select CAST(LEFT(userID, 4) AS int) from audit

- Milacay

---

在表格的UserID列中，只有整数值或任何字符值。例如：像122这样或者像122Adf这样？ - Pandian

UserID列仅包含整数值。谢谢！ - Milacay

Answer 1

您可以尝试更新表格以去除这些字符：

UPDATE dbo.[audit]
  SET UserID = REPLACE(UserID, CHAR(0), '')
  WHERE CHARINDEX(CHAR(0), UserID) > 0;

但同时您也需要修复导致这些错误数据首次进入表格的问题。与此同时，可以尝试以下方法：

SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), ''))
  FROM dbo.[audit];

但这不是长期的解决方案。修复数据（同时也修复数据类型）。如果您无法立即修复数据类型，那么可以通过添加检查约束条件来快速找到罪犯：

ALTER TABLE dbo.[audit]
  ADD CONSTRAINT do_not_allow_stupid_data
  CHECK (CHARINDEX(CHAR(0), UserID) = 0);

编辑

好的，那肯定是一个四位整数，后面跟着六个 CHAR(0)。我发布的解决方法对我来说确实有效：

DECLARE @foo TABLE(UserID VARCHAR(32));
INSERT @foo SELECT 0x31353831000000000000;

-- this succeeds:
SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), '')) FROM @foo;

-- this fails:
SELECT CONVERT(INT, UserID) FROM @foo;

请确认这段代码是否可以独立运行（至少第一个 SELECT 部分）。如果可以，那么您遇到的错误可能来自不同行中的非数字字符。（如果不行，则可能是某个特定 bug 未被修复的构建版本）。为了缩小范围，您可以尝试从以下查询中随机选取值，然后循环逐个字符检查：

SELECT UserID, CONVERT(VARBINARY(32), UserID)
  FROM dbo.[audit]
  WHERE UserID LIKE '%[^0-9]%';

那么随机选择一行，然后将输出粘贴到类似以下查询的查询中：

所以选择一行随机数据，然后将它的输出粘贴到像这样的查询语句中：

DECLARE @x VARCHAR(32), @i INT;
SET @x = CONVERT(VARCHAR(32), 0x...); -- paste the value here
SET @i = 1;
WHILE @i <= LEN(@x)
BEGIN
  PRINT RTRIM(@i) + ' = ' + RTRIM(ASCII(SUBSTRING(@x, @i, 1)))
  SET @i = @i + 1;
END

在你遇到除了 CHAR(0) 之外的其他故障行之前，可能需要进行一些试错 - 因为你无法真正过滤掉包含 CHAR(0) 的行，因为它们可能包含 CHAR(0) 和 CHAR(其他内容)。 我们所知道的是，表中可能存在以下值：

SELECT '15' + CHAR(9) + '23' + CHAR(0);

无论您是否替换了CHAR(0)，这也无法转换为整数。

我知道您不想听，但我真的很高兴这对人们来说是痛苦的，因为现在他们有更多的战斗故事可以用来反击那些关于数据类型做出非常糟糕决策的人。

- Aaron Bertrand

---

我尝试了"SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), '')) FROM dbo.[audit];"，但它不起作用。我还尝试了LTRIM和RTRIM，但也不起作用...谢谢帮助。 - Milacay

@Milacay 嗯，你确定 CHAR(0) 是该列中唯一的无效值吗？请找出一行数据使其失败，并告诉我们以下查询语句的输出结果，例如：SELECT CONVERT(VARBINARY(32), UserID) FROM dbo.[audit] WHERE LEFT(UserID, 4) = '1581'; - Aaron Bertrand

我使用了“varbinary（32）…”来运行您的查询，输出结果为：0x31353831000000000000。顺便说一句，这个varchar字段（userID）是从Varbinary（128）转换成Varchar（128），现在我想将其转换为INT与不同表上的UserID进行连接...感谢@Aaron - Milacay

非常感谢您，@Aaron。 "SELECT CONVERT(INT，REPLACE（UserID，CHAR（0），''））FROM @foo;" 是有效的。 对于造成的混淆，我深表歉意。 - Milacay

首先，为什么要进行varbinary转换？此外，请注意从varbinary转换为varchar可能会产生不同的结果。请尝试以下查询：SELECT CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo')), CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo'),1), LEN(CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo'))), LEN(CONVERT(VARCHAR(128), CONVERT(VARBINARY(128), 'foo'),1)); - Aaron Bertrand

Answer 2

这个问题已经有91,000次的浏览，因此可能有很多人正在寻找一个更通用的解决方案来解决标题中的问题"error converting varchar to INT"

如果您使用的是SQL Server 2012+，处理这些无效数据的一种方法是使用TRY_CAST

SELECT TRY_CAST (userID AS INT)
FROM   audit 

在之前的版本中，您可以使用

SELECT CASE
         WHEN ISNUMERIC(RTRIM(userID) + '.0e0') = 1
              AND LEN(userID) <= 11
           THEN CAST(userID AS INT)
       END
FROM   audit 

如果值无法转换，则两者都将返回NULL。

在你提出的这个具体情况中，我建议使用以下方法。

CAST(REPLACE(userID COLLATE Latin1_General_Bin, CHAR(0),'') AS INT)

尝试替换空字符通常会出现问题，除非使用二进制排序。

- Martin Smith

---

请问为什么加上“.0e0”后它就变成了数值类型？ - Tigran

@Tigran 这并不使它成为数字。添加这个可以帮助验证一个已经是数字的字符串是否是整数。例如，123 以 123.0e0 的形式写入时会通过，但 123.45 会失败，因为 123.45.0e0 不是有效的。 - Martin Smith

Answer 3

这更适用于正在寻找结果的人，而不是原帖发布者。这对我有用...

declare @value varchar(max) = 'sad';
select sum(cast(iif(isnumeric(@value) = 1, @value, 0) as bigint));

returns 0

declare @value varchar(max) = '3';
select sum(cast(iif(isnumeric(@value) = 1, @value, 0) as bigint));

returns 3

- Eric Brueggemann

Answer 4

我建议您尝试对数字进行修剪以查看结果：

select len(rtrim(ltrim(userid))) from audit

如果返回正确的值，那么就执行以下操作：

select convert(int, rtrim(ltrim(userid))) from audit

如果这并未返回正确的值，那么我会使用replace函数来去除空格:

 select convert(int, replace(userid, char(0), '')) from audit

- Avitus

---

我尝试了所有的LTRIM、RTRIM和用''替换char(0)，但还是不行。很奇怪！谢谢。 - Milacay

Answer 5

这是我在我的情况下解决问题的方法：

首先，我确保要转换为整数的列不包含任何空格：

update data set col1 = TRIM(col1)

我也检查了该列是否只包含数字。
你可以通过以下方式进行检查：

select * from data where col1 like '%[^0-9]%' order by col1

如果存在任何非数字值，您可以将它们保存到另一个表中并从您正在处理的表中删除它们。

select * into nonnumeric_data from data where col1 like '%[^0-9]%'
delete from data where col1 like '%[^0-9]%'

我的数据出了问题，就是上面提到的情况。所以在修复了它们之后，我创建了一个bigint变量，并将varchar列的值设置为我创建的整数列。

alter table data add int_col1 bigint
update data set int_col1 = CAST(col1 AS VARCHAR)

这对我有用，希望你也觉得有用。

- Leyla Alkan