[解決済み] SQL Server : varchar を INT に変換する。

質問

を変換するのに行き詰っています。 varchar 列 UserID へ INT . そうなんです、なぜこの UserID カラムは、最初はINTとして作成されていませんでした。

そこで、この方法を試してみたのですが、うまくいきません。

select CAST(userID AS int) from audit

エラーです。

varchar 値を変換する際に、変換に失敗しました。 '1581......................................................』からデータ型 int に変換しています。

した。 select len(userID) from audit と表示され、スペース以外の128文字が返されます。

ID番号の後に続くものをASCII文字として検出しようとしたところ、ASCII値＝0となりました。

また LTRIM , RTRIM を置き換える。 char(0) を '' が、うまくいきません。

唯一、以下のように固定文字数を伝えると動作するのですが 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

OK、確かに4桁の整数の後にCHAR(0)が6つ続いていますね。そして、私が投稿した回避策は、間違いなく私のために動作します。

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) と CHAR(something else) . 私たちが知っている限りでは、あなたはテーブルの中に次のような値を持っています。

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

...これも整数に変換できません。 CHAR(0) というように。

しかし、私は、このことが人々にとって苦痛であることを本当に嬉しく思っています。なぜなら、人々がデータ型について非常に誤った判断をしたときに、それを押し返すための戦利品がより多く手に入るようになったからです。