CAST e IsNumeric

Por que a seguinte consulta retornará “Erro ao converter o tipo de dados varchar para bigint”? IsNumeric não faz o CAST seguro? Eu tentei todos os tipos de dados numéricos no casting e obtive o mesmo erro “Erro ao converter …”. Não acredito que o tamanho do número resultante seja um problema porque estouro é um erro diferente.

O interessante é que, no estúdio de gerenciamento, os resultados realmente aparecem no painel de resultados por uma fração de segundo antes que o erro retorne.

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

Alguma ideia?

IsNumeric retorna 1 se o valor varchar puder ser convertido em QUALQUER tipo de número. Isso inclui int, bigint, decimal, numérico, real e flutuante.

A notação científica pode estar causando um problema. Por exemplo:

 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 

Existe um truque que você pode usar com IsNumeric para que ele retorne 0 para números com notação científica. Você pode aplicar um truque semelhante para evitar valores decimais.

IsNumeric (YourColumn + ‘e0’)

IsNumeric (YourColumn + ‘.0e0’)

Experimente.

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

Fundo:

Eu uso um database de terceiros que constantemente recebe novos dados de outros fornecedores de terceiros.
É meu trabalho analisar um horrível campo varchar usado para armazenar resultados.
Queremos analisar o máximo de dados possível e essa solução mostra como você pode “limpar” os dados para que as inputs válidas não sejam ignoradas.

  1. Alguns resultados são enviados por texto livre.
  2. Algumas são Enumerações (Sim, Não, Azul, Preto, etc.).
  3. Alguns são inteiros.
  4. Outros usam decimais.
  5. Muitas são porcentagens, que se convertidas em um número inteiro podem te atrapalhar mais tarde.

Se eu precisar consultar um determinado intervalo decimal (digamos -1,4 a 3,6, quando aplicável), minhas opções serão limitadas.
Atualizei minha consulta abaixo para usar a sugestão @GMastros para append ‘e0’.
Obrigado @GMastros, isso me salvou duas linhas extras de lógica.

Solução:

 --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. 

Saída:

A captura de canvas abaixo foi formatada e cortada para caber no StackOverflow.
Os resultados reais têm mais colunas. IsNumeric Casting de MikeTeeVee

Pesquisa:

Ao lado de cada consulta é o resultado.
É interessante ver as limitações do IsNumeric, bem como as limitações do CASTing.
Eu mostro isso para que você possa ver a pesquisa de fundo que foi escrita na consulta acima.
É importante entender cada decisão de design (caso você esteja pensando em cortar qualquer coisa).

 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. 

A melhor solução seria parar de armazenar inteiros em uma coluna varchar. Claramente há um problema de dados em que os dados são interpretáveis ​​como numéricos, mas não podem ser convertidos como tal. Você precisa encontrar o (s) registro (s) que é (são) o problema e corrigi-los se os dados forem tais que possam e devam ser corrigidos. Dependendo do que você está armazenando e por que é um varchar para começar, talvez seja necessário corrigir a consulta em vez dos dados. Mas isso também será mais fácil se você encontrar os registros que estão explodindo sua consulta atual.

Como fazer isso é o problema. É relativamente fácil procurar por uma casa decimal nos dados para ver se você tem decimais (diferente de.0 que seria convertido) usando charindex. Você também pode procurar por qualquer registro contendo e ou $ ou qualquer outro caractere que possa ser interpolado como numérico de acordo com as fonts já fornecidas. Se você não tiver muitos registros, uma rápida varredura visual dos dados provavelmente os encontrará, especialmente se você classificar primeiro esse campo.

Às vezes, quando fico preso ao encontrar os dados incorretos que estão explodindo uma consulta, eu coloco os dados em uma tabela temporária e, em seguida, tento processar em lotes (usando interpolação) até encontrar o que ele explode. Comece com os primeiros 1000 (não esqueça de usar order by ou você não obterá os mesmos resultados quando excluir os bons registros e 1000 será apenas um palpite se você tiver milhões de registros com um número maior). Se for aprovado, exclua esses 1000 registros e selecione o próximo lote. Quando falhar, selecione um lote menor. Quando estiver com um número que possa ser facilmente digitalizado, você encontrará o problema. Consegui encontrar registros de problemas com bastante rapidez quando tenho milhões de registros e um erro estranho que nenhuma das consultas que tentei (que são basicamente adivinhações sobre o que pode estar errado) encontrou o problema.

ISNUMERIC é apenas … estúpido. Você não deveria usá-lo. Todos os casos abaixo do retorno 1:

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

Para qualquer tipo inteiro usando: ISNUMERIC(@Value) = 1 só usar: (@Value NOT LIKE '[^0-9]') OR (@Value NOT LIKE '-[^0-9]'

A única boa solução é não usar ISNUMERIC.

Experimente e veja se você ainda recebe um erro …

 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 

Tente envolvê-lo em um caso:

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

De acordo com BOL ISNUMERIC retorna 1 quando a expressão de input é avaliada para um tipo de dados numérico válido; caso contrário, retorna 0.

Tipos de dados numéricos válidos incluem o seguinte:

  • int
  • numérico
  • bigint
  • dinheiro
  • pequena
  • smallmoney
  • tinyint
  • flutuador
  • decimal
  • real

Então, como os outros apontaram, você terá alguns dados que passarão no teste ISNUMERIC , mas falharão na transmissão para bigint

Eu tive o mesmo problema e eu vim com a function escalar como Im em 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 

Se você estiver em 2012, você pode usar TRY_CONVERT

Eu tive o mesmo problema no MSSQL 2014 desencadeado por uma vírgula em vez de ponto final: isnumeric (‘9090,23’) dá 1; cast (‘9090,23’ como float) falha

Eu substituí ‘,’ por ‘.’

existem funções DAX (IsError ou IfError) que podem ajudar nessa situação, mas não as temos em nosso SQL Server 2008 R2. Parece algum pacote de análise adicional para o SQL Server.

Me deparei com este post que pode ajudar. Não me deparei com esse problema antes e não tenho certeza se isso ajudará você nessa instância:

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