Servidor SQL: a converter o varchar para INT
Estou preso na conversão de uma coluna varchar para INT. Eu sei, por favor, não pergunte por que essa coluna não foi criada como INT inicialmente, uma longa história.
select CAST(userID AS int) from audit
Erro:
a conversão falhou ao converter o valor varchar '1581............................................................................................................................"aos dados digite int.
fiz select len(userID) from audit e devolve 128 caracteres, que não são espaços.
tentei detectar caracteres ASCII para aqueles que seguem depois do número de identificação e valor ASCII = 0.
actualizado!!!
Eu também tentei.LTRIM, RTRIM, e substituir char(0) por '', mas não funciona.
a única maneira de funcionar quando eu digo o número fixo de caracteres como este abaixo, mas {[3] } nem sempre são 4 caracteres.
select CAST(LEFT(userID, 4) AS int) from audit
4 answers
Pode tentar actualizar a tabela para se livrar destes caracteres:
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);
EDITAR
Ok, então é definitivamente um inteiro de 4 dígitos seguido por seis instâncias de CHAR (0). E o trabalho que eu postei definitivamente funciona para mim:
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;
Por favor, confirme que este código por si só (bem, o primeiro SELECT, de qualquer forma) funciona para si. Se isso acontecer, então o erro que você está recebendo é de um diferente caráter não-numérico em uma linha diferente (e se não acontecer, então talvez você tenha uma compilação onde um bug particular não foi corrigido). Para tentar reduzi-lo, você pode pegar valores aleatórios da seguinte consulta e, em seguida, fazer um loop através dos caracteres:
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
Isto pode levar algum teste e erro antes de você encontrar uma linha que falha por alguma outra razão que não CHAR(0) - uma vez que você não pode realmente filtrar as linhas que contêm CHAR(0) porque elas podem conter CHAR(0) e CHAR(something else). Por tudo o que sabemos você tem valores em a tabela como:
SELECT '15' + CHAR(9) + '23' + CHAR(0);
...que também não pode ser convertido para um inteiro, quer tenha substituído CHAR(0) ou não.
Esta questão tem 91.000 visualizações, por isso talvez muitas pessoas estejam à procura de uma solução mais genérica para a questão no título "erro convertendo varchar para INT"
Se estiver no servidor SQL 2012+ uma forma de lidar com estes dados inválidos é usar TRY_CAST
SELECT TRY_CAST (userID AS INT)
FROM audit
Nas versões anteriores você poderia usar
SELECT CASE
WHEN ISNUMERIC(RTRIM(userID) + '.0e0') = 1
AND LEN(userID) <= 11
THEN CAST(userID AS INT)
END
FROM audit
Ambos retornam NULL se o valor não puder ser moldado.
No caso específico que tem na sua pergunta com valores negativos conhecidos, eu usaria o seguinte: entanto.
CAST(REPLACE(userID COLLATE Latin1_General_Bin, CHAR(0),'') AS INT)
Tentar substituir o carácter nulo é muitas vezes problemático, excepto se usar uma colação binária.
Eu tentaria testar o número para ver o que você tem:
select len(rtrim(ltrim(userid))) from audit
Se isso devolver o valor correcto, então apenas faça:
select convert(int, rtrim(ltrim(userid))) from audit
Se isso não devolver o valor correcto, então eu faria uma substituição para remover o espaço vazio:
select convert(int, replace(userid, char(0), '')) from audit
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