SQL TSQL - 将字符串转换为整数或返回默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2000045/
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
TSQL - Cast string to integer or return default value
提问by Oliver Hanappi
Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails?
T-SQL 中有没有办法将 nvarchar 转换为 int 并在转换失败时返回默认值或 NULL？
采纳答案by Joseph Sturtevant
If you are on SQL Server 2012 (or newer):
如果您使用的是 SQL Server 2012（或更新版本）：
Use the TRY_CONVERT function.
If you are on SQL Server 2005, 2008, or 2008 R2:
如果您使用的是 SQL Server 2005、2008 或 2008 R2：
Create a user defined function. This will avoid the issues that Fedor Hajdu mentionedwith regards to currency, fractional numbers, etc:
创建用户定义的函数。这将避免Fedor Hajdu 提到的有关货币、小数等的问题：
CREATE FUNCTION dbo.TryConvertInt(@Value varchar(18))
RETURNS int
AS
BEGIN
SET @Value = REPLACE(@Value, ',', '')
IF ISNUMERIC(@Value + 'e0') = 0 RETURN NULL
IF ( CHARINDEX('.', @Value) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 ) RETURN NULL
DECLARE @I bigint =
CASE
WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(bigint, PARSENAME(@Value, 2))
ELSE CONVERT(bigint, @Value)
END
IF ABS(@I) > 2147483647 RETURN NULL
RETURN @I
END
GO
-- Testing
DECLARE @Test TABLE(Value nvarchar(50)) -- Result
INSERT INTO @Test SELECT '1234' -- 1234
INSERT INTO @Test SELECT '1,234' -- 1234
INSERT INTO @Test SELECT '1234.0' -- 1234
INSERT INTO @Test SELECT '-1234' -- -1234
INSERT INTO @Test SELECT '34' -- NULL
INSERT INTO @Test SELECT '1234e10' -- NULL
INSERT INTO @Test SELECT '1234 5678' -- NULL
INSERT INTO @Test SELECT '123-456' -- NULL
INSERT INTO @Test SELECT '1234.5' -- NULL
INSERT INTO @Test SELECT '123456789000000' -- NULL
INSERT INTO @Test SELECT 'N/A' -- NULL
SELECT Value, dbo.TryConvertInt(Value) FROM @Test
Reference:I used this pageextensively when creating my solution.
参考：我在创建解决方案时广泛使用了这个页面。
回答by Grzegorz Gierlik
Yes :). Try this:
是的 ：）。尝试这个：
DECLARE @text AS NVARCHAR(10)
SET @text = '100'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns 100
SET @text = 'XXX'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns NULL
ISNUMERIC()has a few issues pointed by Fedor Hajdu.
ISNUMERIC()Fedor Hajdu指出了一些问题。
It returns true for strings like $(is currency), ,or .(both are separators), +and -.
对于像$(iscurrency), ,or .(两者都是分隔符), +and 之类的字符串，它返回 true -。
回答by Fedor Hajdu
I would rather create a function like TryParse or use T-SQL TRY-CATCHblock to get what you wanted.
我宁愿创建一个像 TryParse 这样的函数或使用 T-SQLTRY-CATCH块来获得你想要的。
ISNUMERIC doesn't always work as intended. The code given before will fail if you do:
ISNUMERIC 并不总是按预期工作。如果您这样做，之前给出的代码将失败：
SET @text = '$'
SET @text = '$'
$ sign can be converted to money datatype, so ISNUMERIC()returns true in that case. It will do the same for '-' (minus), ',' (comma) and '.' characters.
$ 符号可以转换为货币数据类型，因此ISNUMERIC()在这种情况下返回 true。'-'（减号）、','（逗号）和 '.' 也是如此。人物。
回答by Douglas
As has been mentioned, you may run into several issues if you use ISNUMERIC:
如前所述，如果您使用，您可能会遇到几个问题ISNUMERIC：
-- Incorrectly gives 0:
SELECT CASE WHEN ISNUMERIC('-') = 1 THEN CAST('-' AS INT) END
-- Error (conversion failure):
SELECT CASE WHEN ISNUMERIC('$') = 1 THEN CAST('$' AS INT) END
SELECT CASE WHEN ISNUMERIC('4.4') = 1 THEN CAST('4.4' AS INT) END
SELECT CASE WHEN ISNUMERIC('1,300') = 1 THEN CAST('1,300' AS INT) END
-- Error (overflow):
SELECT CASE WHEN ISNUMERIC('9999999999') = 1 THEN CAST('9999999999' AS INT) END
If you want a reliable conversion, you'll need to code one yourself.
如果您想要可靠的转换，则需要自己编写代码。
Update: My new recommendation would be to use an intermediary test conversion to FLOATto validate the number. This approach is based on adrianm's comment. The logic can be defined as an inline table-valued function:
更新：我的新建议是使用中间测试转换FLOAT来验证数字。这种方法基于阿德里安的评论。该逻辑可以定义为内联表值函数：
CREATE FUNCTION TryConvertInt (@text NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT
CASE WHEN ISNUMERIC(@text + '.e0') = 1 THEN
CASE WHEN CONVERT(FLOAT, @text) BETWEEN -2147483648 AND 2147483647
THEN CONVERT(INT, @text)
END
END AS [Result]
)
Some tests:
一些测试：
SELECT [Conversion].[Result]
FROM ( VALUES
( '1234' ) -- 1234
, ( '1,234' ) -- NULL
, ( '1234.0' ) -- NULL
, ( '-1234' ) -- -1234
, ( '34' ) -- NULL
, ( '1234e10' ) -- NULL
, ( '1234 5678' ) -- NULL
, ( '123-456' ) -- NULL
, ( '1234.5' ) -- NULL
, ( '123456789000000' ) -- NULL
, ( 'N/A' ) -- NULL
, ( '-' ) -- NULL
, ( '$' ) -- NULL
, ( '4.4' ) -- NULL
, ( '1,300' ) -- NULL
, ( '9999999999' ) -- NULL
, ( '00000000000000001234' ) -- 1234
, ( '212110090000000235698741' ) -- NULL
) AS [Source] ([Text])
OUTER APPLY TryConvertInt ([Source].[Text]) AS [Conversion]
Results are similar to Joseph Sturtevant's answer, with the following main differences:
结果与Joseph Sturtevant 的回答相似，主要区别如下：
- My logic does not tolerate occurrences of
.or,in order to mimic the behaviour of nativeINTconversions.'1,234'and'1234.0'returnNULL. - Since it does not use local variables, my function can be defined as an inline table-valued function, allowing for better query optimization.
- Joseph's answer can lead to incorrect results due to silent truncations of the argument;
'00000000000000001234'evaluates to12. Increasing the parameter length would result in errors on numbers that overflowBIGINT, such as BBANs(basic bank account numbers) like'212110090000000235698741'.
- 我的逻辑不容忍
.或,为了模仿本机INT转换的行为。'1,234'并'1234.0'返回NULL。 - 由于它不使用局部变量，我的函数可以定义为内联表值函数，以便更好地优化查询。
- 由于参数的无声截断，Joseph 的答案可能会导致不正确的结果；
'00000000000000001234'评估为12. 增加参数长度会导致溢出的数字错误BIGINT，例如BBAN（基本银行帐号），如'212110090000000235698741'.
Withdrawn: The approach below is no longer recommended, as is left just for reference.
撤回：不再推荐以下方法，仅供参考。
The snippet below works on non-negative integers. It checks that your string does not contain any non-digit characters, is not empty, and does not overflow (by exceeding the maximum value for the inttype). However, it also gives NULLfor valid integers whose length exceeds 10 characters due to leading zeros.
下面的代码段适用于非负整数。它会检查您的字符串是否不包含任何非数字字符、不为空且不溢出（通过超过该int类型的最大值）。但是，它也提供NULL了由于前导零而长度超过 10 个字符的有效整数。
SELECT
CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 1 AND 9
OR LEN(@text) = 10 AND @text <= '2147483647'
THEN CAST (@text AS INT)
END
END
If you want to support any number of leading zeros, use the below. The nested CASEstatements, albeit unwieldy, are required to promote short-circuit evaluation and reduce the likelihood of errors (arising, for example, from passing a negative length to LEFT).
如果您想支持任意数量的前导零，请使用以下内容。嵌套CASE语句虽然笨拙，但需要促进短路评估并减少出错的可能性（例如，将负长度传递给LEFT）。
SELECT
CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 1 AND 9 THEN CAST (@text AS INT)
WHEN LEN(@text) >= 10 THEN
CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
AND RIGHT(@text, 10) <= '2147483647'
THEN CAST (@text AS INT)
END
END
END
If you want to support positive andnegative integers with any number of leading zeros:
如果要支持具有任意数量前导零的正整数和负整数：
SELECT
-- Positive integers (or 0):
CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 1 AND 9 THEN CAST (@text AS INT)
WHEN LEN(@text) >= 10 THEN
CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
AND RIGHT(@text, 10) <= '2147483647'
THEN CAST (@text AS INT)
END
END
-- Negative integers:
WHEN LEFT(@text, 1) = '-' THEN
CASE WHEN RIGHT(@text, LEN(@text) - 1) NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 2 AND 10 THEN CAST (@text AS INT)
WHEN LEN(@text) >= 11 THEN
CASE WHEN SUBSTRING(@text, 2, LEN(@text) - 11) NOT LIKE '%[^0]%'
AND RIGHT(@text, 10) <= '2147483648'
THEN CAST (@text AS INT)
END
END
END
END
回答by JotaPardo
Regards.
问候。
I wrote a useful scalar function to simulate the TRY_CAST function of SQL SERVER 2012 in SQL Server 2008.
我写了一个有用的标量函数来模拟SQL Server 2008中SQL SERVER 2012的TRY_CAST函数。
You can see it in the next link below and we help each other to improve it. TRY_CAST Function for SQL Server 2008https://gist.github.com/jotapardo/800881eba8c5072eb8d99ce6eb74c8bb
您可以在下面的下一个链接中看到它，我们会互相帮助改进它。SQL Server 2008 的 TRY_CAST 函数https://gist.github.com/jotapardo/800881eba8c5072eb8d99ce6eb74c8bb
The two main differences are that you must pass 3 parameters and you must additionally perform an explicit CONVERT or CAST to the field. However, it is still very useful because it allows you to return a default value if CAST is not performed correctly.
两个主要区别是您必须传递 3 个参数，并且必须另外对字段执行显式 CONVERT 或 CAST。但是，它仍然非常有用，因为如果 CAST 未正确执行，它允许您返回默认值。
dbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast)
Example:
例子：
SELECT CASE WHEN dbo.TRY_CAST('6666666166666212', 'INT', DEFAULT) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
For now only supports the data types INT, DATE, NUMERIC, BIT and FLOAT
目前仅支持数据类型INT、DATE、NUMERIC、BIT 和 FLOAT
I hope you find it useful.
希望对你有帮助。
CODE:
代码：
DECLARE @strSQL NVARCHAR(1000)
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TRY_CAST]'))
BEGIN
SET @strSQL = 'CREATE FUNCTION [dbo].[TRY_CAST] () RETURNS INT AS BEGIN RETURN 0 END'
EXEC sys.sp_executesql @strSQL
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
------------------------------------------------------------------------------------------------------------------------
Description:
Syntax
---------------
dbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast)
+---------------------------+-----------------------+
| Expression | VARCHAR(8000) |
+---------------------------+-----------------------+
| Data_Type | VARCHAR(8000) |
+---------------------------+-----------------------+
| ReturnValueIfErrorCast | SQL_VARIANT = NULL |
+---------------------------+-----------------------+
Arguments
---------------
expression
The value to be cast. Any valid expression.
Data_Type
The data type into which to cast expression.
ReturnValueIfErrorCast
Value returned if cast fails or is not supported. Required. Set the DEFAULT value by default.
Return Type
----------------
Returns value cast to SQL_VARIANT type if the cast succeeds; otherwise, returns null if the parameter @pReturnValueIfErrorCast is set to DEFAULT,
or that the user indicates.
Remarks
----------------
dbo.TRY_CAST function simulates the TRY_CAST function reserved of SQL SERVER 2012 for using in SQL SERVER 2008.
dbo.TRY_CAST function takes the value passed to it and tries to convert it to the specified Data_Type.
If the cast succeeds, dbo.TRY_CAST returns the value as SQL_VARIANT type; if the cast doesn′t succees, null is returned if the parameter @pReturnValueIfErrorCast is set to DEFAULT.
If the Data_Type is unsupported will return @pReturnValueIfErrorCast.
dbo.TRY_CAST function requires user make an explicit CAST or CONVERT in ANY statements.
This version of dbo.TRY_CAST only supports CAST for INT, DATE, NUMERIC and BIT types.
Examples
====================================================================================================
--A. Test TRY_CAST function returns null
SELECT
CASE WHEN dbo.TRY_CAST('6666666166666212', 'INT', DEFAULT) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO
--B. Error Cast With User Value
SELECT
dbo.TRY_CAST('2147483648', 'INT', DEFAULT) AS [Error Cast With DEFAULT],
dbo.TRY_CAST('2147483648', 'INT', -1) AS [Error Cast With User Value],
dbo.TRY_CAST('2147483648', 'INT', NULL) AS [Error Cast With User NULL Value];
GO
--C. Additional CAST or CONVERT required in any assignment statement
DECLARE @IntegerVariable AS INT
SET @IntegerVariable = CAST(dbo.TRY_CAST(123, 'INT', DEFAULT) AS INT)
SELECT @IntegerVariable
GO
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (
Id INT IDENTITY
, FieldNumeric NUMERIC(3, 1)
)
INSERT INTO dbo.#temp (FieldNumeric)
SELECT CAST(dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', 0) AS NUMERIC(3, 1));--Need explicit CAST on INSERT statements
SELECT *
FROM #temp
DROP TABLE #temp
GO
--D. Supports CAST for INT, DATE, NUMERIC and BIT types.
SELECT dbo.TRY_CAST(2147483648, 'INT', 0) AS [Cast failed]
, dbo.TRY_CAST(2147483647, 'INT', 0) AS [Cast succeeded]
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST(212, 'INT', 0), 'BaseType') AS [BaseType];
SELECT dbo.TRY_CAST('AAAA0101', 'DATE', DEFAULT) AS [Cast failed]
, dbo.TRY_CAST('20160101', 'DATE', DEFAULT) AS [Cast succeeded]
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST('2016-01-01', 'DATE', DEFAULT), 'BaseType') AS [BaseType];
SELECT dbo.TRY_CAST(1.23, 'NUMERIC(3,1)', DEFAULT) AS [Cast failed]
, dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', DEFAULT) AS [Cast succeeded]
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST(12.3, 'NUMERIC(3,1)', DEFAULT), 'BaseType') AS [BaseType];
SELECT dbo.TRY_CAST('A', 'BIT', DEFAULT) AS [Cast failed]
, dbo.TRY_CAST(1, 'BIT', DEFAULT) AS [Cast succeeded]
, SQL_VARIANT_PROPERTY(dbo.TRY_CAST('123', 'BIT', DEFAULT), 'BaseType') AS [BaseType];
GO
--E. B. TRY_CAST return NULL on unsupported data_types
SELECT dbo.TRY_CAST(4, 'xml', DEFAULT) AS [unsupported];
GO
====================================================================================================
------------------------------------------------------------------------------------------------------------------------
Responsible: Javier Pardo
Date: diciembre 29/2016
WB tests: Javier Pardo
------------------------------------------------------------------------------------------------------------------------
Update by: Javier Eduardo Pardo Moreno
Date: febrero 16/2017
Id update: JEPM20170216
Description: Fix ISNUMERIC function makes it unreliable. SELECT dbo.TRY_CAST('+', 'INT', 0) will yield Msg 8114,
Level 16, State 5, Line 16 Error converting data type varchar to float.
ISNUMERIC() function treats few more characters as numeric, like: – (minus), + (plus), $ (dollar), \ (back slash), (.)dot and (,)comma
Collaborator aperiooculus (http://stackoverflow.com/users/3083382/aperiooculus )
Fix dbo.TRY_CAST('2013/09/20', 'datetime', DEFAULT) for supporting DATETIME format
WB tests: Javier Pardo
------------------------------------------------------------------------------------------------------------------------
*/
ALTER FUNCTION dbo.TRY_CAST
(
@pExpression AS VARCHAR(8000),
@pData_Type AS VARCHAR(8000),
@pReturnValueIfErrorCast AS SQL_VARIANT = NULL
)
RETURNS SQL_VARIANT
AS
BEGIN
--------------------------------------------------------------------------------
-- INT
--------------------------------------------------------------------------------
IF @pData_Type = 'INT'
BEGIN
IF ISNUMERIC(@pExpression) = 1 AND @pExpression NOT IN ('-','+','$','.',',','\') --JEPM20170216
BEGIN
DECLARE @pExpressionINT AS FLOAT = CAST(@pExpression AS FLOAT)
IF @pExpressionINT BETWEEN - 2147483648.0 AND 2147483647.0
BEGIN
RETURN CAST(@pExpressionINT as INT)
END
ELSE
BEGIN
RETURN @pReturnValueIfErrorCast
END --FIN IF @pExpressionINT BETWEEN - 2147483648.0 AND 2147483647.0
END
ELSE
BEGIN
RETURN @pReturnValueIfErrorCast
END -- FIN IF ISNUMERIC(@pExpression) = 1
END -- FIN IF @pData_Type = 'INT'
--------------------------------------------------------------------------------
-- DATE
--------------------------------------------------------------------------------
IF @pData_Type IN ('DATE','DATETIME')
BEGIN
IF ISDATE(@pExpression) = 1
BEGIN
DECLARE @pExpressionDATE AS DATETIME = cast(@pExpression AS DATETIME)
IF @pData_Type = 'DATE'
BEGIN
RETURN cast(@pExpressionDATE as DATE)
END
IF @pData_Type = 'DATETIME'
BEGIN
RETURN cast(@pExpressionDATE as DATETIME)
END
END
ELSE
BEGIN
DECLARE @pExpressionDATEReplaced AS VARCHAR(50) = REPLACE(REPLACE(REPLACE(@pExpression,'\',''),'/',''),'-','')
IF ISDATE(@pExpressionDATEReplaced) = 1
BEGIN
IF @pData_Type = 'DATE'
BEGIN
RETURN cast(@pExpressionDATEReplaced as DATE)
END
IF @pData_Type = 'DATETIME'
BEGIN
RETURN cast(@pExpressionDATEReplaced as DATETIME)
END
END
ELSE
BEGIN
RETURN @pReturnValueIfErrorCast
END
END --FIN IF ISDATE(@pExpression) = 1
END --FIN IF @pData_Type = 'DATE'
--------------------------------------------------------------------------------
-- NUMERIC
--------------------------------------------------------------------------------
IF @pData_Type LIKE 'NUMERIC%'
BEGIN
IF ISNUMERIC(@pExpression) = 1
BEGIN
DECLARE @TotalDigitsOfType AS INT = SUBSTRING(@pData_Type,CHARINDEX('(',@pData_Type)+1, CHARINDEX(',',@pData_Type) - CHARINDEX('(',@pData_Type) - 1)
, @TotalDecimalsOfType AS INT = SUBSTRING(@pData_Type,CHARINDEX(',',@pData_Type)+1, CHARINDEX(')',@pData_Type) - CHARINDEX(',',@pData_Type) - 1)
, @TotalDigitsOfValue AS INT
, @TotalDecimalsOfValue AS INT
, @TotalWholeDigitsOfType AS INT
, @TotalWholeDigitsOfValue AS INT
SET @pExpression = REPLACE(@pExpression, ',','.')
SET @TotalDigitsOfValue = LEN(REPLACE(@pExpression, '.',''))
SET @TotalDecimalsOfValue = CASE Charindex('.', @pExpression)
WHEN 0
THEN 0
ELSE Len(Cast(Cast(Reverse(CONVERT(VARCHAR(50), @pExpression, 128)) AS FLOAT) AS BIGINT))
END
SET @TotalWholeDigitsOfType = @TotalDigitsOfType - @TotalDecimalsOfType
SET @TotalWholeDigitsOfValue = @TotalDigitsOfValue - @TotalDecimalsOfValue
-- The total digits can not be greater than the p part of NUMERIC (p, s)
-- The total of decimals can not be greater than the part s of NUMERIC (p, s)
-- The total digits of the whole part can not be greater than the subtraction between p and s
IF (@TotalDigitsOfValue <= @TotalDigitsOfType) AND (@TotalDecimalsOfValue <= @TotalDecimalsOfType) AND (@TotalWholeDigitsOfValue <= @TotalWholeDigitsOfType)
BEGIN
DECLARE @pExpressionNUMERIC AS FLOAT
SET @pExpressionNUMERIC = CAST (ROUND(@pExpression, @TotalDecimalsOfValue) AS FLOAT)
RETURN @pExpressionNUMERIC --Returns type FLOAT
END
else
BEGIN
RETURN @pReturnValueIfErrorCast
END-- FIN IF (@TotalDigitisOfValue <= @TotalDigits) AND (@TotalDecimalsOfValue <= @TotalDecimals)
END
ELSE
BEGIN
RETURN @pReturnValueIfErrorCast
END --FIN IF ISNUMERIC(@pExpression) = 1
END --IF @pData_Type LIKE 'NUMERIC%'
--------------------------------------------------------------------------------
-- BIT
--------------------------------------------------------------------------------
IF @pData_Type LIKE 'BIT'
BEGIN
IF ISNUMERIC(@pExpression) = 1
BEGIN
RETURN CAST(@pExpression AS BIT)
END
ELSE
BEGIN
RETURN @pReturnValueIfErrorCast
END --FIN IF ISNUMERIC(@pExpression) = 1
END --IF @pData_Type LIKE 'BIT'
--------------------------------------------------------------------------------
-- FLOAT
--------------------------------------------------------------------------------
IF @pData_Type LIKE 'FLOAT'
BEGIN
IF ISNUMERIC(REPLACE(REPLACE(@pExpression, CHAR(13), ''), CHAR(10), '')) = 1
BEGIN
RETURN CAST(@pExpression AS FLOAT)
END
ELSE
BEGIN
IF REPLACE(@pExpression, CHAR(13), '') = '' --Only white spaces are replaced, not new lines
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN @pReturnValueIfErrorCast
END --IF REPLACE(@pExpression, CHAR(13), '') = ''
END --FIN IF ISNUMERIC(@pExpression) = 1
END --IF @pData_Type LIKE 'FLOAT'
--------------------------------------------------------------------------------
-- Any other unsupported data type will return NULL or the value assigned by the user to @pReturnValueIfErrorCast
--------------------------------------------------------------------------------
RETURN @pReturnValueIfErrorCast
END
回答by Brian Payne
My solution to this issue was to create the function shown below. My requirements included that the number had to be a standard integer, not a BIGINT, and I needed to allow negative numbers and positive numbers. I have not found a circumstance where this fails.
我对这个问题的解决方案是创建如下所示的函数。我的要求包括数字必须是标准整数，而不是 BIGINT，而且我需要允许负数和正数。我还没有发现这种情况会失败。
CREATE FUNCTION [dbo].[udfIsInteger]
(
-- Add the parameters for the function here
@Value nvarchar(max)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int = 0
-- Add the T-SQL statements to compute the return value here
DECLARE @MinValue nvarchar(11) = '-2147483648'
DECLARE @MaxValue nvarchar(10) = '2147483647'
SET @Value = ISNULL(@Value,'')
IF LEN(@Value)=0 OR
ISNUMERIC(@Value)<>1 OR
(LEFT(@Value,1)='-' AND LEN(@Value)>11) OR
(LEFT(@Value,1)='-' AND LEN(@Value)=11 AND @Value>@MinValue) OR
(LEFT(@Value,1)<>'-' AND LEN(@Value)>10) OR
(LEFT(@Value,1)<>'-' AND LEN(@Value)=10 AND @Value>@MaxValue)
GOTO FINISHED
DECLARE @cnt int = 0
WHILE @cnt<LEN(@Value)
BEGIN
SET @cnt=@cnt+1
IF SUBSTRING(@Value,@cnt,1) NOT IN ('-','0','1','2','3','4','5','6','7','8','9') GOTO FINISHED
END
SET @Result=1
FINISHED:
-- Return the result of the function
RETURN @Result
END
回答by Stephen Turner
Joseph's answer pointed out ISNUMERIC also handles scientific notation like '1.3e+3' but his answer doesn't handle this format of number.
Joseph 的回答指出 ISNUMERIC 也处理像 '1.3e+3' 这样的科学记数法，但他的回答不处理这种数字格式。
Casting to a money or float first handles both the currency and scientific issues:
铸造货币或浮动首先处理货币和科学问题：
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TryConvertInt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TryConvertInt]
GO
CREATE FUNCTION dbo.TryConvertInt(@Value varchar(18))
RETURNS bigint
AS
BEGIN
DECLARE @IntValue bigint;
IF (ISNUMERIC(@Value) = 1)
IF (@Value like '%e%')
SET @IntValue = CAST(Cast(@Value as float) as bigint);
ELSE
SET @IntValue = CAST(CAST(@Value as money) as bigint);
ELSE
SET @IntValue = NULL;
RETURN @IntValue;
END
The function will fail if the number is bigger than a bigint.
如果数字大于 bigint，该函数将失败。
If you want to return a different default value, leave this function so it is generic and replace the null afterwards:
如果要返回不同的默认值，请保留此函数使其通用，然后替换 null：
SELECT IsNull(dbo.TryConvertInt('nan') , 1000);
回答by Ramblen Man
I know it's not pretty but it is simple. Try this:
我知道它不漂亮，但它很简单。尝试这个：
declare @AlpaNumber nvarchar(50) = 'ABC'
declare @MyNumber int = 0
begin Try
select @MyNumber = case when ISNUMERIC(@AlpaNumber) = 1 then cast(@AlpaNumber as int) else 0 end
End Try
Begin Catch
-- Do nothing
End Catch
if exists(select * from mytable where mynumber = @MyNumber)
Begin
print 'Found'
End
Else
Begin
print 'Not Found'
End
