SQL Server - 检查是否可以进行数据类型转换

Question

我有以下代码将nvarchar转换为整数：

     cast(@value as int)

然而，我无法控制参数@value，因此代码可能会失败。有没有办法在进行强制转换之前检查是否可以进行强制转换？

- Bruce

Answer 1

在SQL Server 2012中，您可以使用新的TRY_CAST()函数。但是，对于SQL Server 2008，您可以使用ISNUMERIC()函数，并包含处理未通过该测试的值的代码。

- Matt

---

ISNumeric('a1a')返回1，但是你永远无法将('a1a' as float)转换。 - CodingTT

我相信你指的是SQL Server 2014: https://msdn.microsoft.com/zh-cn/library/hh974669.aspx - Daniel Cotter

它存在于两个版本中。有一个下拉菜单可以更改版本 - https://msdn.microsoft.com/zh-cn/library/hh974669(v=sql.110).aspx - Matt

Answer 2

我最近回答了一个关于这个问题的问题，使用ISNUMERIC进行CAST到一个INT本身是行不通的。原因是，ISNUMERIC对于非整数数字（例如1.5）返回true。

以下是有关此主题的最近答案：

https://stackoverflow.com/a/14692165/1073631

考虑添加使用CHARINDEX和ISNUMERIC进行附加检查，或者使用正则表达式来验证数据（我更喜欢后者）。

这里是一个演示，说明单独使用ISNUMERIC存在的问题。这里是一个使用正则表达式而不是ISNUMERIC的演示，可以正常工作。

DECLARE @Test nvarchar(10)
SET @Test = '1.5'
--Works
SELECT CASE WHEN @Test NOT LIKE '%[^0-9]%' THEN CAST(@Test as int) ELSE 0 END 
-- Produces Error
SELECT CASE WHEN ISNUMERIC(@Test) = 1 THEN CAST(@Test as int) ELSE 0 END 

祝你好运。

- sgeddes

---

+1……第一个是正确的。你的回答应该更清晰，第二个是错误的（我必须滚动才能看到“产生错误”）。 - Gordon Linoff

你还应该防止溢出（并可选择允许负整数）：https://dev59.com/8nI-5IYBdhLWcg3wI0t9#24250511 - Douglas

Answer 3

我通常使用以下内容，它似乎适用于所有情况。

SELECT CASE WHEN 1 = ISNUMERIC(@value + '.0') THEN CAST(@value as int) ELSE 0 END

此方法利用了“ISNUMERIC”函数不允许两个句点的特性。但在SQL Server 2012及以上版本中，“TRY_CAST”函数是更好的解决方案。

- Michael Erickson

---

迄今为止预2012数据库的最佳答案。 - Martin Davies

Answer 4

适当的测试应该是：

select (case when isnumeric(val) = 1 and val not like '%e%' and val not like '%.%'
             then cast(val as int)
        end)

isnumeric()函数会对看起来像浮点数的任何内容返回1，因此您需要小心。

您还可以使用我认为是SQL Server的一个奇特之处。您可以将浮点值1.23强制转换为int类型，但无法将字符串值强制转换。因此，以下内容也有效：

select (case when isnumeric(val) = 1
             then cast(cast(val as float) as int)
        end)

- Gordon Linoff

---

不，这是错误的。isnumeric('+') == 1。isnumeric('-') == 1。isnumeric 是一个可怕的实现，而他们的答案会让你面对多个边缘情况，在这些情况下 cast 到 int 尽管你进行了检查仍然失败。 - mattmc3

@mattmc3 . . 你有没有注意到OP基本上接受了这个答案。此外，转换为“float”可以解决许多问题（尽管不能解决溢出问题）。 - Gordon Linoff

被接受的答案对于 SQL 2012+ 的 TRY_CAST 部分是正确的。要使用 ISNUMERIC，您必须进行大量的体操来处理边缘情况。例如，将“+”或“-”插入“val”作为值。转换为 float 将会失败。（^^不是我的踩 - 我更喜欢评论而不是声誉损失）。这个答案提供了一种使用 ISNUMERIC 的方法，实际上处理了边缘情况，并确保您有一个合适的 TRY_CAST 替代方案：https://dev59.com/xW855IYBdhLWcg3wIAga - mattmc3

Answer 5

也许我们可以做这样的事情：

declare @value as nvarchar(10) = 'A';

begin try
    select cast(@value as int);
end try
begin catch
-- do something
end catch

- Anoop Verma

---

我喜欢这种方法，但我们不能在SELECT语句内部执行，对吧？ - Nate Anderson

我已经调查并测试了它。它不符合我的需求。这只打印特定的错误信息。 ERROR_NUMBER()作为ErrorNumber， ERROR_STATE()作为ErrorState， ERROR_SEVERITY()作为ErrorSeverity， ERROR_PROCEDURE()作为ErrorProcedure， ERROR_LINE()作为ErrorLine， ERROR_MESSAGE()作为ErrorMessage; 但是，它不能打印出能够识别记录的我的自定义错误消息。 - givonz

Answer 6

使用带有TRY CATCH块的过程来抑制错误。

例如：

CREATE PROCEDURE p_try_cast
 @type nvarchar(MAX),
 @value nvarchar(MAX)
AS
BEGIN

    BEGIN TRY
        DECLARE @sql        varchar(MAX)
        DECLARE @out_table  TABLE(value varchar(MAX))

        SET @sql = 'SELECT  CONVERT(varchar(max), CAST(''' + @value + ''' AS ' + @type + '))'

        INSERT  @out_table
        EXECUTE (@sql)

        IF EXISTS ( SELECT 1 FROM @out_table WHERE value = @value)
            RETURN 1

        RETURN 0
    END TRY
    BEGIN CATCH
        RETURN 0
    END CATCH

END

GO

现在，您可以使用传递的字符串和所需类型调用它，该过程返回成功的1和失败的0。

DECLARE @ret int

-- This returns 0 - Fail
EXEC @ret = p_try_cast 'integer', '1.5'

-- This returns 1 - Success
EXEC @ret = p_try_cast 'integer', '1.5'

-- This returns 0 - Fail
EXEC @ret = p_try_cast 'char(4)', 'HELLO'

-- This returns 1 - Success
EXEC @ret = p_try_cast 'char(4)', 'HELL'

- cmdr_putin