SQL案例声明不适用于ISNUMERIC检查 [英] SQL case statment not working for ISNUMERIC check

问题描述

下面是我的脚本，但它给我的错误是

消息8114，等级16，状态5，行1

将数据类型varchar转换为数字时出错。 />


脚本

Below is my script but its giving me error as
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

script

create table #temp ( strAmount varchar(50))
  insert into #temp values('123A')
  insert into #temp values('123.00')
  select * from #temp

  select
 CASE
    When ISNUMERIC(strAmount)=1 then 
       CONVERT(Numeric(10,2),strAmount)
    else 
       strAmount
  End
 from #temp

推荐答案

首先查看您的数据。

当您的列中的数据无法转换为数字时，SQL报告将数据类型varchar转换为数字时出错，因为它包含的字符是不是数字：例如'A'到'Z'。



然后，当你找到坏数据时 - 更改数据库以便存储数字数字列中的值。如果你不这样做，那么这个问题将会再次出现 - 再次出现。除字母数据外，切勿使用VARCHAR或NVARCHAR列。 DATE列中的日期，数字是数字列。否则，您只是在以后开始找到问题时自己存储问题！

So start by looking at your data.
SQL reports "Error converting data type varchar to numeric" when the data in your column cannot be converted to a number because it contains characters that are not numeric digits: 'A' to 'Z' for example.

Then, when you have found the "bad" data - change your database so that you store numeric values in numeric columns. If you don't, then this problem will come back - again, and again, and again. Never use VARCHAR or NVARCHAR columns except of alphabetic data. Dates in DATE columns, number is numeric columns. Otherwise you are just storing up problems for yourself later as you are starting to find here!

create table #temp ( strAmount varchar(50))
  insert into #temp values('123A')
  insert into #temp values('123.00')
  select * from #temp

  select
 CASE
    When ISNUMERIC(strAmount)=1 then
       CONVERT(varchar(50),CONVERT(Numeric(10,2),strAmount))
    else
       strAmount
  End
 from #temp

当您在表中插入货币值时，

当输入表达式求值为有效数值数据时，ISNUMERIC返回1类型;否则它返回0.有效的数字数据类型是

As you are inserting monetary value in the table,
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types are

int

numeric

bigint

money

smallint

smallmoney

tinyint

float

decimal

real

参考： https://msdn.microsoft.com/en-us/library/ms186272.aspx [ ^ ]







因此第一项的isnumeric返回false，第二项则返回true。



当它为真时，你试图将它转换为数字而实际值不是真正的数字，因为它包含'

Ref: https://msdn.microsoft.com/en-us/library/ms186272.aspx[^]



so isnumeric returns false for the first item and true for the second.

When it is true, you are trying to convert it to number whereas the actual value is not really a number as it contains '

这篇关于SQL案例声明不适用于ISNUMERIC检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！