sql server - 检查是否可以进行强制转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14719760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql server - check to see if cast is possible
提问by Bruce
I have the following code to cast nvarchar to integer:
我有以下代码将 nvarchar 转换为整数：
cast(@value as int)
However I have no control of the parameter @value, hence the code might fail. Is there anyway to check if a cast is possible before doing a cast?
但是我无法控制参数@value，因此代码可能会失败。无论如何，在进行演员表之前是否可以检查是否可以进行演员表？
回答by Matt
Well, in SQL Server 2012 you could use the new TRY_CAST(), but with SQL Server 2008, you should be able to use ISNUMERIC(), and then include handling for values that do not pass that test.
好吧，在 SQL Server 2012 中，您可以使用新的TRY_CAST()，但在 SQL Server 2008 中，您应该能够使用ISNUMERIC()，然后包括对未通过该测试的值的处理。
回答by sgeddes
I've recently answered a question about this and using ISNUMERICto CASTto an INTwon't work by itself. Reason being, ISNUMERICreturns true for non integer numbers (1.5) for example.
我最近回答了这个和使用问题ISNUMERIC，以CAST一种INT不会自行工作。原因是，例如，ISNUMERIC对于非整数 (1.5) 返回 true。
Here was a recent answer on the subject:
这是有关该主题的最新答案：
https://stackoverflow.com/a/14692165/1073631
https://stackoverflow.com/a/14692165/1073631
Consider adding an additional check using CHARINDEX with ISNUMERIC, or what I prefer, use a Regular Expression to validate the data.
考虑使用带有 ISNUMERIC 的 CHARINDEX 添加额外的检查，或者我更喜欢使用正则表达式来验证数据。
And here is a Fiddledemonstrating the problem with using ISNUMERIC on it's own. And the Fiddleusing a regular expression instead that works.
这是一个Fiddle，演示了单独使用 ISNUMERIC 的问题。而Fiddle使用正则表达式来代替它。
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
Good luck.
祝你好运。
回答by Michael Erickson
I generally use the following, it seems to cover all the situations.
我一般用下面的，好像涵盖了所有的情况。
SELECT CASE WHEN 1 = ISNUMERIC(@value + '.0') THEN CAST(@value as int) ELSE 0 END
It takes advantage of the fact that "ISNUMERIC" will not allow two periods. The "TRY_CAST" in SQL Server 2012+ is a much better solution though.
它利用了“ISNUMERIC”不允许两个句点的事实。不过，SQL Server 2012+ 中的“TRY_CAST”是一个更好的解决方案。
回答by Gordon Linoff
The proper test is:
正确的测试是：
select (case when isnumeric(val) = 1 and val not like '%e%' and val not like '%.%'
then cast(val as int)
end)
The function isnumeric()returns 1 for anything that looks like a float, so you have to be careful.
isnumeric()对于任何看起来像浮点数的东西，该函数返回 1，所以你必须小心。
You can also use what I consider to be a peculiarity of SQL Server. You can cast the floating value 1.23 to an int, but you cannot cast the string value. So, the following also works:
您还可以使用我认为是 SQL Server 的一个特性。您可以将浮点值 1.23 转换为 int，但不能转换字符串值。因此，以下也有效：
select (case when isnumeric(val) = 1
then cast(cast(val as float) as int)
end)
回答by Anoop Verma
Maybe we can do something like this:
也许我们可以这样做：
declare @value as nvarchar(10) = 'A';
begin try
select cast(@value as int);
end try
begin catch
-- do something
end catch
回答by cmdr_putin
Use a procedure with a TRY CATCH block to suppress errors
使用带有 TRY CATCH 块的过程来抑制错误
i.e.
IE
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
Now you can call that with the passed string and desired type and the proc returns 1 for success and 0 for failure
现在您可以使用传递的字符串和所需的类型调用它，proc 返回 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'
