TSQL - 将字符串转换为整数或返回默认值 [英] TSQL - Cast string to integer or return default value

问题描述

在 T-SQL 中有没有办法将 nvarchar 转换为 int 并在转换失败时返回默认值或 NULL?

Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails?

推荐答案

如果您使用的是 SQL Server 2012(或更新版本):

使用 TRY_CONVERT 函数.

创建一个用户定义的函数.这将避免Fedor Hajdu 提到的有关货币、小数等的问题:

Create a user defined function. This will avoid the issues that Fedor Hajdu mentioned with regards to currency, fractional numbers, etc:

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 '$1234'           -- 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 page extensively when creating my solution.

这篇关于TSQL - 将字符串转换为整数或返回默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！