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
Obrigado.
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;
Mas depois também terá de corrigir o que quer que esteja a colocar estes dados maus na mesa. Entretanto, talvez tente:
SELECT CONVERT(INT, REPLACE(UserID, CHAR(0), ''))
FROM dbo.[audit];
Mas isso não é uma solução a longo prazo. Corrigir os dados (e o tipo de dados enquanto você está nele). Se você não pode corrigir o tipo de dados imediatamente, então você pode rapidamente encontrar o culpado, adicionando uma verificação restrição:
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]%';
Por isso, pegue numa linha aleatória, e depois cole o resultado numa consulta como esta:
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