TSQL - Cast string to integer or return default value

Question

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

Answer 1

If you are using SQL Server 2012 (or newer version) then, use the TRY_CONVERT function.

But, if you are using SQL Server 2005, 2008, or 2008 R2, then Try to create a user-defined function. This will avoid the issues of fractional numbers.

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

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

For more information, you can refer to: https://www.tek-tips.com/faqs.cfm?fid=6423