代码之家 › 专栏 › 技术社区 › Mark Bostleman

Cast和IsNumeric

sql-server-2005 sql-server

Mark Bostleman · 技术社区 · 16 年前

为什么下面的查询会返回“将数据类型varchar转换为bigint时出错”?是不是numeric让演员安全了?我尝试了演员表中的每个数字数据类型,得到了相同的“错误转换…”错误。我不认为结果数字的大小有问题,因为溢出是另一个错误。

有趣的是,在ManagementStudio中,在返回错误之前,结果实际上会在结果窗格中显示一秒钟。

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

有什么想法吗?

11 回复 | 直到 8 年前

George Mastros 16 年前

如果varchar值可以转换为任何数字类型,则isnumeric返回1。这包括int、bigint、decimal、numeric、real&float。

科学记数法可能会给你带来问题。例如:

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

有一个技巧可以与isNumeric一起使用,以便它为带有科学记数法的数字返回0。您可以应用类似的技巧来防止十进制值。

IsNumeric(您的列+'e0')

IsNumeric(您的列+'.0e0')

试试看。

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

MikeTeeVee ageektrapped 11 年前

背景:

我使用第三方数据库,它不断地从其他第三方供应商那里接收新数据。
我的工作是解析一个用于存储结果的可怕的varchar字段。
我们希望尽可能多地解析数据,这个解决方案向您展示了如何“清理”数据,以便有效的条目不会被忽略。

一些结果是免费发送的。
有些是枚举(是、否、蓝色、黑色等)。
有些是整数。
其他的使用小数。
许多是百分比,如果转换成整数,以后可能会绊倒您。

如果我需要查询给定的十进制范围(如果适用,可以说-1.4到3.6),我的选项是有限的。
我更新了下面的查询,使用@gmastros建议附加“e0”。
谢谢@gmastros,这为我节省了额外的2行逻辑。

解决方案:

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

输出:

下面的屏幕截图已格式化并被缩减以适应stackoverflow。
实际结果有更多的列。 MikeTeeVee's IsNumeric Casting

研究:

在每个查询的旁边是结果。
有趣的是,看到IsNumeric的缺点以及铸件的局限性。
我展示了这一点,这样您就可以看到编写上述查询的背景研究。
理解每个设计决策是很重要的(以防你想删掉任何东西)。

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.

HLGEM 16 年前

最好的解决方案是停止在varchar列中存储整数。显然,存在一个数据问题,即数据可以解释为数字,但不能强制转换为数字。您需要找到问题所在的记录,并在数据可以且应该修复的情况下修复它们。根据您要存储的内容以及它是varchar的原因,您可能需要修复查询而不是数据。但是,如果您首先找到了正在刷新当前查询的记录,这也将更容易做到。

如何做到这一点是个问题。使用charindex搜索数据中的小数点比较容易,以查看是否有小数(除了.0之外,它将转换)。您还可以查找包含e或$的任何记录,或者根据已经给出的源代码,可以用数字形式插入的任何其他字符。如果您没有很多记录,对数据进行快速的视觉扫描可能会找到它,特别是如果您首先对该字段进行排序。

有时,当我一直在寻找破坏查询的坏数据时,我把数据放入一个临时表,然后尝试成批地处理(使用插值),直到找到它爆炸的那个。从前1000条开始(不要忘记使用ORDER BY,否则删除好的记录时不会得到相同的结果,如果有数百万条记录以较大的数字开始,1000条只是一个最佳猜测)。如果通过,删除1000条记录并选择下一批。一旦失败,选择一个较小的批次。一旦你找到一个容易被肉眼扫描的数字,你就会发现问题所在。当我有数以百万计的记录和一个wierd错误时,我能够相当快地找到问题记录,我尝试过的所有查询(基本上都是关于什么可能是错误的猜测)都没有发现问题。

jcolebrand 11 年前

试试看你是否仍然有错误…

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

Arkady 12 年前

IsNumeric只是…愚蠢的。你根本不应该用它。以下所有情况返回1:

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

对于任何整数类型,请使用: ISNUMERIC(@Value) = 1 只需使用: (@Value NOT LIKE '[^0-9]') OR (@Value NOT LIKE '-[^0-9]'

唯一好的解决方案是不要使用isNumeric。

Dalin Seivewright 16 年前

尝试将其包装在一个案例中:

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

kristof 16 年前

根据 BOL 当输入表达式的计算结果为有效的数值数据类型时,IsNumeric返回1;否则返回0。

有效的数字数据类型包括:

int
数字的
大整数
钱
短整型
货币型
蒂尼特
浮动
十进制的
真实的

因此,正如其他人指出的,您将拥有一些将通过的数据 等号的 对bigint进行测试但失败

Rohit Vipin Mathews 11 年前

我有同样的问题,我在2008年的SQL中提出了标量函数im。

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

如果你在2012年,你可以使用 TRY_CONVERT

user5480949 9 年前

我在2014年的MSSQL中也遇到过同样的问题,是由逗号而不是句号触发的: IsNumeric(‘9090,23’)给出1; CAST(“9090,23”作为浮动)失败

我把''替换为''。

Greg 8 年前

在这种情况下,有DAX函数(ISeror或iError)可以提供帮助,但我们的SQL Server 2008 R2上没有这些函数。看起来像是一些用于SQL Server的附加分析包。

-1

BenAlabaster 16 年前

我看到了这篇可能会有所帮助的博客文章。我以前没有遇到过这个问题,也不确定它是否对您有帮助:

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