try - SQL Server: convertir varchar a INT

trycast sql server 2008

Question

Estoy atascado en la conversión de una columna varchar UserID a INT . Lo sé, por favor, no preguntes por qué esta columna UserID no se creó como INT inicialmente, historia larga.

Así que intenté esto, pero no funciona. y dame un error

select CAST(userID AS int) from audit

Error:

La conversión falló al convertir el valor de varchar ''1581 ......................................... .................................................. ................................. ''al tipo de datos int.

select len(userID) from audit y devuelve 128 caracteres, que no son espacios.

Intenté detectar caracteres ASCII para aquellos que se encuentran detrás del número de ID y el valor ASCII = 0.

¡¡¡Actualizado!!!

También probé LTRIM , RTRIM y reemplace char(0) con '''' , pero no funciona.

La única forma en que funciona es cuando digo el número fijo de caracteres como este a continuación, pero UserID no es siempre 4 caracteres.

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

Gracias,

Answer 1

Esta pregunta tiene 91,000 visitas, por lo que quizás muchas personas estén buscando una solución más genérica para el problema en el título "error al convertir varchar a INT".

Si está en SQL Server 2012+, una forma de manejar estos datos no válidos es usar TRY_CAST

SELECT TRY_CAST (userID AS INT)
FROM audit

En versiones anteriores podías usar

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

Ambos devuelven NULL si el valor no se puede convertir.

Sin embargo, en el caso específico que tenga en su pregunta con valores malos conocidos, usaría lo siguiente.

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

Tratar de reemplazar el carácter nulo suele ser problemático, excepto si se usa una intercalación binaria.

Answer 2

Esto es más para alguien que busca un resultado, que el post-post original. Esto me funcionó ...

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

Answer 3

Intentaría recortar el número para ver lo que obtienes:

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

Si eso devuelve el valor correcto, simplemente haz:

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

si eso no devuelve el valor correcto, entonces haría un reemplazo para eliminar el espacio vacío:

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

Answer 4

Puedes intentar actualizar la tabla para deshacerte de estos personajes:

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

Pero luego, en primer lugar, también tendrá que corregir lo que sea que pone estos datos erróneos en la tabla. Mientras tanto, quizás intente:

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

Pero esa no es una solución a largo plazo. Arregla los datos (y el tipo de datos mientras estás en ello). Si no puede corregir el tipo de datos inmediatamente, puede encontrar rápidamente al culpable agregando una restricción de verificación:

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

EDITAR

Ok, entonces ese es definitivamente un entero de 4 dígitos seguido de seis instancias de CHAR (0). Y la solución que publiqué definitivamente funciona para mí:

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;

Confirme que este código por sí solo (bueno, el primer SELECT , de todos modos) funciona para usted. Si lo hace, entonces el error que está obteniendo es de un carácter no numérico diferente en una fila diferente (y si no lo hace, entonces quizás tenga una compilación donde no se ha solucionado un error en particular). Para intentar restringirlo, puede tomar valores aleatorios de la siguiente consulta y luego recorrer los caracteres:

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

Así que tome una fila aleatoria y luego pegue la salida en una 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

Esto puede requerir un poco de prueba y error antes de encontrar una fila que falle por alguna otra razón que no sea CHAR(0) , ya que realmente no puede filtrar las filas que contienen CHAR(0) porque podrían contener CHAR(0) y CHAR(something else) . Por todo lo que sabemos tenemos valores en la tabla como:

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

... que tampoco se puede convertir en un entero, ya sea que hayas reemplazado a CHAR(0) o no.

Sé que no quieres escucharlo, pero estoy realmente contento de que esto sea doloroso para las personas, porque ahora tienen más historias de guerra que rechazar cuando las personas toman decisiones muy pobres sobre los tipos de datos.