SQL Server：将 varchar 转换为 INT

我被困在转换varchar column UserID to INT。我知道，请不要问为什么UserID专栏最初并不是作为 INT 创建的，说来话长。

所以我尝试了这个，但它不起作用。并给我一个错误：

select CAST(userID AS int) from audit

Error:

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

I did select len(userID) from audit它返回 128 个字符，其中不是空格。

我尝试检测 ID 号后面的 ASCII 字符并且 ASCII 值 = 0。

我也尝试过LTRIM, RTRIM，并替换char(0) with ''，但不起作用。

当我像下面这样告诉固定数量的字符时，它起作用的唯一方法是，但是UserID并不总是 4 个字符。

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

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

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);

EDIT

好的，这肯定是一个 4 位整数，后跟 6 个 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，无论如何）适合你。如果确实如此，那么您收到的错误来自不同行中的不同非数字字符（如果没有，那么您可能有一个尚未修复特定错误的构建）。要尝试缩小范围，您可以从以下查询中获取随机值，然后循环遍历字符：

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) and CHAR(something else)。据我们所知，表中的值如下：

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

...无论你是否替换了，它也无法转换为整数CHAR(0) or not.

我知道你不想听，但我真的很高兴这对人们来说是痛苦的，因为现在当人们对数据类型做出非常糟糕的决定时，他们有更多的战争故事可以反击。