CAST e IsNumeric

¿Por qué la siguiente consulta devolverá “Error al convertir el tipo de datos varchar a bigint”? ¿IsNumeric no es seguro para CAST? Probé todos los tipos de datos numéricos en el molde y obtuve el mismo error de “Error al convertir …”. No creo que el tamaño del número resultante sea un problema porque el desbordamiento es un error diferente.

Lo interesante es que, en el estudio de gestión, los resultados realmente aparecen en el panel de resultados durante una fracción de segundo antes de que vuelva el error.

SELECT CAST(myVarcharColumn AS bigint) FROM myTable WHERE IsNumeric(myVarcharColumn) = 1 AND myVarcharColumn IS NOT NULL GROUP BY myVarcharColumn 

¿Alguna idea?

IsNumeric devuelve 1 si el valor varchar se puede convertir a CUALQUIER tipo de número. Esto incluye int, bigint, decimal, numérico, real y float.

La notación científica podría estar causando un problema. Por ejemplo:

 Declare @Temp Table(Data VarChar(20)) Insert Into @Temp Values(NULL) Insert Into @Temp Values('1') Insert Into @Temp Values('1e4') Insert Into @Temp Values('Not a number') Select Cast(Data as bigint) From @Temp Where IsNumeric(Data) = 1 And Data Is Not NULL 

Hay un truco que puede usar con IsNumeric para que devuelva 0 para los números con notación científica. Puede aplicar un truco similar para evitar valores decimales.

IsNumeric (YourColumn + ‘e0’)

IsNumeric (YourColumn + ‘.0e0’)

Pruébalo.

 SELECT CAST(myVarcharColumn AS bigint) FROM myTable WHERE IsNumeric(myVarcharColumn + '.0e0') = 1 AND myVarcharColumn IS NOT NULL GROUP BY myVarcharColumn 

Fondo:

Utilizo una base de datos de terceros que constantemente recibe nuevos datos de otros proveedores de terceros.
Es mi trabajo analizar un campo varchar horrendo utilizado para almacenar resultados.
Queremos analizar la mayor cantidad de datos posible, y esta solución muestra cómo puede “limpiar” los datos para que no se pasen por alto las entradas válidas.

  1. Algunos resultados son de texto libre.
  2. Algunos son enumeraciones (sí, no, azul, negro, etc.).
  3. Algunos son enteros.
  4. Otros usan decimales.
  5. Muchos son porcentajes, que si se convierten a un número entero podrían hacerlo tropezar más tarde.

Si necesito consultar un rango decimal dado (digamos -1.4 a 3.6 cuando corresponda), mis opciones son limitadas.
Actualicé mi consulta a continuación para usar la sugerencia de @GMastros para agregar ‘e0’.
Gracias @GMastros, esto me ahorró 2 líneas extra de lógica.

Solución:

 --NOTE: I'd recommend you use this to convert your numbers and store them in a separate table (or field). -- This way you may reuse them when when working with legacy/3rd-party systems, instead of running these calculations on the fly each time. SELECT Result.Type, Result.Value, Parsed.CleanValue, Converted.Number[Number - Decimal(38,4)], (CASE WHEN Result.Value IN ('0', '1', 'True', 'False') THEN CAST(Result.Value as Bit) ELSE NULL END)[Bit],--Cannot convert 1.0 to Bit, it must be in Integer format already. (CASE WHEN Converted.Number BETWEEN 0 AND 255 THEN CAST(Converted.Number as TinyInt) ELSE NULL END)[TinyInt], (CASE WHEN Converted.Number BETWEEN -32768 AND 32767 AND Result.Value LIKE '%\%%' ESCAPE '\' THEN CAST(Converted.Number / 100.0 as Decimal(9,4)) ELSE NULL END)[Percent], (CASE WHEN Converted.Number BETWEEN -32768 AND 32767 THEN CAST(Converted.Number as SmallInt) ELSE NULL END)[SmallInt], (CASE WHEN Converted.Number BETWEEN -214748.3648 AND 214748.3647 THEN CAST(Converted.Number as SmallMoney) ELSE NULL END)[SmallMoney], (CASE WHEN Converted.Number BETWEEN -2147483648 AND 2147483647 THEN CAST(Converted.Number as Int) ELSE NULL END)[Int], (CASE WHEN Converted.Number BETWEEN -2147483648 AND 2147483647 THEN CAST(CAST(Converted.Number as Decimal(10)) as Int) ELSE NULL END)[RoundInt],--Round Up or Down instead of Truncate. (CASE WHEN Converted.Number BETWEEN -922337203685477.5808 AND 922337203685477.5807 THEN CAST(Converted.Number as Money) ELSE NULL END)[Money], (CASE WHEN Converted.Number BETWEEN -9223372036854775808 AND 9223372036854775807 THEN CAST(Converted.Number as BigInt) ELSE NULL END)[BigInt], (CASE WHEN Parsed.CleanValue IN ('1', 'True', 'Yes', 'Y', 'Positive', 'Normal') THEN CAST(1 as Bit) WHEN Parsed.CleanValue IN ('0', 'False', 'No', 'N', 'Negative', 'Abnormal') THEN CAST(0 as Bit) ELSE NULL END)[Enum], --I couln't use just Parsed.CleanValue LIKE '%e%' here because that would match on "True" and "Negative", so I also had to match on only allowable characters. - 02/13/2014 - MCR. (CASE WHEN ISNUMERIC(Parsed.CleanValue) = 1 AND Parsed.CleanValue LIKE '%e%' THEN Parsed.CleanValue ELSE NULL END)[Exponent] FROM ( VALUES ('Null', NULL), ('EmptyString', ''), ('Spaces', ' - 2 . 8 % '),--Tabs and spaces mess up IsNumeric(). ('Bit', '0'), ('TinyInt', '123'), ('Int', '123456789'), ('BigInt', '1234567890123456'), --('VeryLong', '12345678901234567890.1234567890'), ('VeryBig', '-1234567890123456789012345678901234.5678'), ('TooBig', '-12345678901234567890123456789012345678.'),--34 (38-4) is the Longest length of an Integer supported by this query. ('VeryLong', '-1.2345678901234567890123456789012345678'), ('TooLong', '-12345678901234567890.1234567890123456789'),--38 Digits is the Longest length of a Number supported by the Decimal data type. ('VeryLong', '000000000000000000000000000000000000001.0000000000000000000000000000000000000'),--Works because Casting ignores leading zeroes. ('TooLong', '.000000000000000000000000000000000000000'),--Exceeds the 38 Digit limit for all Decimal types after the decimal-point. --Dot(.), Plus(+), Minus(-), Comma(,), DollarSign($), BackSlash(\), Tab(0x09), and Letter-E(e) all yeild false-posotives with IsNumeric(). ('Decimal', '.'), ('Decimal', '.0'), ('Decimal', '3.99'), ('Positive', '+'), ('Positive', '+20'), ('Negative', '-'), ('Negative', '-45'), ('Negative', '- 1.23'), ('Comma', ','), ('Comma', '1,000'), ('Money', '$'), ('Money', '$10'), ('Percent', '%'), ('Percent', '110%'),--IsNumeric will kick out Percent(%) signs. ('BkSlash', '\'), ('Tab', CHAR(0x09)),--I've actually seen tab characters in our data. ('Exponent', 'e0'), ('Exponent', '100e-999'),--No SQL-Server datatype could hold this number, though it is real. ('Enum', 'True'), ('Enum', 'Negative') ) AS Result(Type, Value)--O is for Observation. CROSS APPLY ( --This Step is Optional. If you have Very Long numbers with tons of leading zeros, then this is useful. Otherwise is overkill if all the numbers you want have 38 or less digits. --Casting of trailing zeros count towards the max 38 digits Decimal can handle, yet Cast ignores leading-zeros. This also cleans up leading/trailing spaces. - 02/25/2014 - MCR. SELECT LTRIM(RTRIM(SUBSTRING(Result.Value, PATINDEX('%[^0]%', Result.Value + '.'), LEN(Result.Value))))[Value] ) AS Trimmed CROSS APPLY ( SELECT --You will need to filter out other Non-Keyboard ASCII characters (before Space(0x20) and after Lower-Case-z(0x7A)) if you still want them to be Cast as Numbers. - 02/15/2014 - MCR. REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Trimmed.Value,--LTRIM(RTRIM(Result.Value)), (CHAR(0x0D) + CHAR(0x0A)), ''),--Believe it or not, we have people that press carriage return after entering in the value. CHAR(0x09), ''),--Apparently, as people tab through controls on a page, some of them inadvertently entered Tab's for values. ' ', ''),--By replacing spaces for values (like '- 2' to work), you open the door to values like '00 12 3' - your choice. '$', ''), ',', ''), '+', ''), '%', ''), '/', '')[CleanValue] ) AS Parsed--P is for Parsed. CROSS APPLY ( --NOTE: I do not like my Cross-Applies to feed into each other. -- I'm paranoid it might affect performance, but you may move this into the select above if you like. - 02/13/2014 - MCR. SELECT (CASE WHEN ISNUMERIC(Parsed.CleanValue + 'e0') = 1--By concatenating 'e0', I do not need to check for: Parsed.CleanValue NOT LIKE '%e%' AND Parsed.CleanValue NOT IN ('.', '-') -- If you never plan to work with big numbers, then could use Decimal(19,4) would be best as it only uses 9 storage bytes compared to the 17 bytes that 38 precision requires. -- This might help with performance, especially when converting a lot of data. AND CHARINDEX('.', REPLACE(Parsed.CleanValue, '-', '')) - 1 <= (38-4)--This is the Longest Integer supported by Decimal(38,4)). AND LEN(REPLACE(REPLACE(Parsed.CleanValue, '-', ''), '.', '')) <= 38--When casting to a Decimal (of any Precision) you cannot exceed 38 Digits. - 02/13/2014 - MCR. THEN CAST(Parsed.CleanValue as Decimal(38,4))--Scale of 4 used is the max that Money has. This is the biggest number SQL Server can hold. ELSE NULL END)[Number] ) AS Converted--C is for Converted. 

Salida:

La siguiente captura de pantalla fue formateada y cortada para ajustarse a StackOverflow.
Los resultados reales tienen más columnas. Casting IsNumeric de MikeTeeVee

Investigación:

Al lado de cada consulta es el resultado.
Es interesante ver las deficiencias de IsNumeric y las limitaciones de CASTING.
Le muestro esto para que pueda ver la investigación de antecedentes que se utilizó para escribir la consulta anterior.
Es importante entender cada decisión de diseño (en caso de que esté pensando en eliminar algo).

 SELECT ISNUMERIC('')--0. This is understandable, but your logic may want to default these to zero. SELECT ISNUMERIC(' ')--0. This is understandable, but your logic may want to default these to zero. SELECT ISNUMERIC('%')--0. SELECT ISNUMERIC('1%')--0. SELECT ISNUMERIC('e')--0. SELECT ISNUMERIC(' ')--1. --Tab. SELECT ISNUMERIC(CHAR(0x09))--1. --Tab. SELECT ISNUMERIC(',')--1. SELECT ISNUMERIC('.')--1. SELECT ISNUMERIC('-')--1. SELECT ISNUMERIC('+')--1. SELECT ISNUMERIC('$')--1. SELECT ISNUMERIC('\')--1. ' SELECT ISNUMERIC('e0')--1. SELECT ISNUMERIC('100e-999')--1. No SQL-Server datatype could hold this number, though it is real. SELECT ISNUMERIC('3000000000')--1. This is bigger than what an Int could hold, so code for these too. SELECT ISNUMERIC('1234567890123456789012345678901234567890')--1. Note: This is larger than what the biggest Decimal(38) can hold. SELECT ISNUMERIC('- 1')--1. SELECT ISNUMERIC(' 1 ')--1. SELECT ISNUMERIC('True')--0. SELECT ISNUMERIC('1/2')--0. No love for fractions. SELECT CAST('e0' as Int)--0. Surpise! Casting to Decimal errors, but for Int is gives us zero, which is wrong. SELECT CAST('0e0' as Int)--0. Surpise! Casting to Decimal errors, but for Int is gives us zero, which is wrong. SELECT CAST(CHAR(0x09) as Decimal(12,2))--Error converting data type varchar to numeric. --Tab. SELECT CAST(' 1' as Decimal(12,2))--Error converting data type varchar to numeric. --Tab. SELECT CAST(REPLACE(' 1', CHAR(0x09), '') as Decimal(12,2))--Error converting data type varchar to numeric. --Tab. SELECT CAST('' as Decimal(12,2))--Error converting data type varchar to numeric. SELECT CAST('' as Int)--0. Surpise! Casting to Decimal errors, but for Int is gives us zero, which is wrong. SELECT CAST(',' as Decimal(12,2))--Error converting data type varchar to numeric. SELECT CAST('.' as Decimal(12,2))--Error converting data type varchar to numeric. SELECT CAST('-' as Decimal(12,2))--Arithmetic overflow error converting varchar to data type numeric. SELECT CAST('+' as Decimal(12,2))--Arithmetic overflow error converting varchar to data type numeric. SELECT CAST('$' as Decimal(12,2))--Error converting data type varchar to numeric. SELECT CAST('$1' as Decimal(12,2))--Error converting data type varchar to numeric. SELECT CAST('1,000' as Decimal(12,2))--Error converting data type varchar to numeric. SELECT CAST('- 1' as Decimal(12,2))--Error converting data type varchar to numeric. (Due to spaces). SELECT CAST(' 1 ' as Decimal(12,2))--1.00 Leading and trailing spaces are okay. SELECT CAST('1.' as Decimal(12,2))--1.00 SELECT CAST('.1' as Decimal(12,2))--0.10 SELECT CAST('-1' as Decimal(12,2))--1.00 SELECT CAST('+1' as Decimal(12,2))--1.00 SELECT CAST('True' as Bit)--1 SELECT CAST('False' as Bit)--0 --Proof: The Casting to Decimal cannot exceed 38 Digits, even if the precision is well below 38. SELECT CAST('1234.5678901234567890123456789012345678' as Decimal(8,4))--1234.5679 SELECT CAST('1234.56789012345678901234567890123456789' as Decimal(8,4))--Arithmetic overflow error converting varchar to data type numeric. --Proof: Casting of trailing zeros count towards the max 38 digits Decimal can handle, yet it ignores leading-zeros. SELECT CAST('.00000000000000000000000000000000000000' as Decimal(8,4))--0.0000 --38 Digits after the decimal point. SELECT CAST('000.00000000000000000000000000000000000000' as Decimal(8,4))--0.0000 --38 Digits after the decimal point and 3 zeros before the decimal point. SELECT CAST('.000000000000000000000000000000000000000' as Decimal(8,4))--Arithmetic overflow error converting varchar to data type numeric. --39 Digits after the decimal point. SELECT CAST('1.00000000000000000000000000000000000000' as Decimal(8,4))--Arithmetic overflow error converting varchar to data type numeric. --38 Digits after the decimal point and 1 non-zero before the decimal point. SELECT CAST('000000000000000000000000000000000000001.0000000000000000000000000000000000000' as Decimal(8,4))--1.0000 --Caveats: When casting to an Integer: SELECT CAST('3.0' as Int)--Conversion failed when converting the varchar value '3.0' to data type int. --NOTE: When converting from character data to Int, you may want to do a double-conversion like so (if you want to Round your results first): SELECT CAST(CAST('3.5' as Decimal(10)) as Int)--4. Decimal(10) has no decimal precision, so it rounds it to 4 for us BEFORE converting to an Int. SELECT CAST(CAST('3.5' as Decimal(11,1)) as Int)--3. Decimal (11,1) HAS decimal precision, so it stays 3.5 before converting to an Int, which then truncates it. --These are the best ways to go if you simply want to Truncate or Round. SELECT CAST(CAST('3.99' as Decimal(10)) as Int)--3. Good Example of Rounding. SELECT CAST(FLOOR('3.99') as Int)--3. Good Example fo Truncating. 

La mejor solución sería dejar de almacenar enteros en una columna varchar. Claramente, hay un problema de datos donde los datos se pueden interpretar como numéricos, pero no se pueden convertir como tales. Necesita encontrar el (los) registro (s) que es (son) el problema y corregirlos si los datos son tales que pueden y deben corregirse. Dependiendo de lo que está almacenando y por qué es un varchar para empezar, es posible que necesite corregir la consulta en lugar de los datos. Pero eso será más fácil de hacer también si primero encuentra los registros que están explotando su consulta actual.

Cómo hacer eso es el problema. Es relativamente fácil buscar un lugar decimal en los datos para ver si tiene decimales (distintos de 0 que se convertirían) usando charindex. También puede buscar cualquier registro que contenga e o $ o cualquier otro carácter que pueda ser interpretado como numérico de acuerdo con las fonts ya proporcionadas. Si no tiene muchos registros, probablemente lo encuentre un escaneo visual rápido de los datos, especialmente si ordena primero en ese campo.

A veces, cuando me he quedado estancado al encontrar los datos incorrectos que explotan una consulta, coloqué los datos en una tabla temporal y luego intenté procesarlos en lotes (utilizando la interpolación) hasta que encuentre el que explota. Comience con los primeros 1000 (no se olvide de usar el orden por o no obtendrá los mismos resultados cuando elimine los buenos registros y 1000 es solo una mejor estimación si tiene millones de registros que comienzan con un número mayor). Si pasa, elimine esos 1000 registros y seleccione el siguiente lote. Una vez que falla, seleccione un lote más pequeño. Una vez que haya elegido un número que pueda escanearse visualmente fácilmente, encontrará el problema. He podido encontrar registros de problemas bastante rápido cuando tengo millones de registros y un error extraño que ninguna de las consultas que he probado (que básicamente son suposiciones sobre qué podría estar mal) han encontrado el problema.

ISNUMERIC es simplemente … estúpido. Deberías usarlo en absoluto. Todos los casos debajo del retorno 1:

 ISNUMERIC('-') ISNUMERIC('.') ISNUMERIC('-$.') 

Para cualquier tipo de entero que utilice: ISNUMERIC(@Value) = 1 solo use: (@Value NOT LIKE '[^0-9]') OR (@Value NOT LIKE '-[^0-9]'

La única buena solución es no usar ISNUMERIC.

Pruebe esto y vea si todavía obtiene un error …

 SELECT CAST(CASE WHEN IsNumeric(myVarcharColumn) = 0 THEN 0 ELSE myVarcharColumn END AS BIGINT) FROM myTable WHERE IsNumeric(myVarcharColumn) = 1 AND myVarcharColumn IS NOT NULL GROUP BY myVarcharColumn 

Intenta envolverlo en un estuche:

 select CASE WHEN IsNumeric(mycolumn) = 1 THEN CAST(mycolumn as bigint) END FROM stack_table WHERE IsNumeric(mycolumn) = 1 GROUP BY mycolumn 

Según BOL, ISNUMERIC devuelve 1 cuando la expresión de entrada evalúa a un tipo de datos numéricos válido; de lo contrario, devuelve 0.

Los tipos de datos numéricos válidos incluyen los siguientes:

  • En t
  • numérico
  • bigint
  • dinero
  • smallint
  • poco dinero
  • tinyint
  • flotador
  • decimal
  • real

Entonces, como otros señalaron, usted tendrá algunos datos que pasarán la prueba ISNUMÉRICA pero fallarán en la conversión a bigint

Tuve el mismo problema y se me ocurrió la función escalar como Im en 2008 SQL

 ALTER Function [dbo].[IsInteger](@Value VarChar(18)) Returns Bit As Begin Return IsNull( (Select Case When CharIndex('.', @Value) > 0 Then 0 Else 1 End Where IsNumeric(@Value + 'e0') = 1), 0) End 

Si estás en 2012, podrías usar TRY_CONVERT

Tuve el mismo problema en MSSQL 2014 desencadenado por una coma en lugar de punto final: isnumeric (‘9090,23’) da 1; el molde (‘9090,23’ como flotante) falla

He reemplazado ‘,’ con ‘.’

existen funciones DAX (IsError o IfError) que podrían ayudar en esta situación, pero no las tenemos en nuestro SQL Server 2008 R2. Parece un paquete de análisis adicional para SQL Server.

Me encontré con esta publicación de blog que podría ayudar. No me he encontrado con este problema antes y no estoy seguro si te ayudará en esta instancia:

http://dotmad.blogspot.com/2007/02/cannot-call-methods-on-bigint-error.html