Tsql - Cast string to integer or return default value

sql

try-catch

sql-server

data-type

byNikita Barsukov·Dec 30, 2024

To convert a string to an integer, use TRY_CAST which attempts the cast and provides NULL if unsuccessful. To substitute any NULLs with a specific value, use ISNULL:

SELECT ISNULL(TRY_CAST(columnName AS INT), defaultValue) FROM tableName;

Replace columnName, defaultValue, and tableName with your details. This one-liner ensures any unconvertible strings default to your specific value. Quick, clean and efficient.

Improved conversion techniques

SQL Server 2012+: TRY_CONVERT

SQL Server 2012 onwards? Use TRY_CONVERT to customize the data type check. If conversion cannot occur, you are given a NULL to swap with a default value.

SELECT ISNULL(TRY_CONVERT(INT, columnName), defaultValue) FROM tableName;
-- Comment: If at first you don't succeed, try_convert again!

SQL Server 2005-2008 R2: User-Defined Function

For earlier versions not supporting TRY_CAST, consider a User-Defined Function (UDF), which safely manages special characters and overflows.

CREATE FUNCTION dbo.TryCastInt(@value NVARCHAR(MAX), @default INT)
RETURNS INT
AS
BEGIN
    IF ISNUMERIC(@value + 'e0') = 1
        RETURN CASE
            WHEN @value NOT LIKE '%[^0-9]%' AND CONVERT(BIGINT, @value) BETWEEN -2147483648 AND 2147483647
            THEN CAST(@value AS INT)
            ELSE @default
        END
    RETURN @default
END
GO
-- Comment: Never upset your UDF, it has the power to crash your program!

Bulletproof conversion: TRY-CATCH

Embed a TRY-CATCH block within stored procedures for a watertight solution, managing conversion exceptions.

BEGIN TRY 
    SELECT CAST(columnName AS INT) FROM tableName;
END TRY
BEGIN CATCH 
    SELECT defaultValue;
END CATCH
-- Comment: Try... catch me if you can!

Dealing with fractions and unwanted characters

Dealing with fractions or separators? Use functions like PARSENAME.

SELECT CAST(PARSENAME(REPLACE(columnName, '.', ''), 1) AS INT)
-- Comment: PARSENAME, because fractions don't have to be a fraction of your sanity.

To remove extraneous characters, use string manipulation:

SELECT ISNULL(TRY_CAST(REPLACE(REPLACE(columnName, '$', ''), ',', '') AS INT), defaultValue) FROM tableName;

Navigating ISNUMERIC's quirks

Counteract ISNUMERIC false positives by adjusting your logic to avoid common traps:

SELECT CASE 
    WHEN label LIKE '%[^0-9]%' THEN 0
    WHEN ISNUMERIC(label + 'e0') = 1 THEN CAST(label AS INT)
    ELSE 0
END
-- Comment: Defeat is simply the addition of wisdom to an existing set of experiences.

Preventing overflows

Stay alert for integral range overflows. Verify that conversion result lies within the expected integer range (-2147483648 to 2147483647):

SELECT CASE 
    WHEN TRY_CONVERT(BIGINT, label) BETWEEN -2147483648 AND 2147483647 THEN CAST(label AS INT)
    ELSE 0 -- Default value on out-of-range
END
-- Comment: Out of range? Please don't try to crash the server!

Checking your cogs and wheels

Ensure that your conversion logic can withstand all expected inputs. Run a set of controlled tests. It reinforces accuracy and reliability:

-- Test Samples
DECLARE @testData TABLE (label NVARCHAR(MAX));
INSERT INTO @testData VALUES ('1234'), ('2147483648'), ('-2147483649'), ('123.45'), ('@#!');

-- Run the conversion test
SELECT label, dbo.TryCastInt(label, 0)
FROM @testData;
-- Comment: Trust me, I'm a programmer. I tested this once!