How to Safely Cast Strings to Integers in T-SQL with Default Values or NULL?

TSQL: Casting Strings to Integers with Default Value or NULL

Casting strings to integers is a common task in T-SQL. However, when the conversion is not possible, an error may occur. To handle such situations, T-SQL provides a useful approach that allows you to return a default value or NULL if the conversion fails.

Solution

The solution involves using the ISNUMERIC() function to check if the string can be converted to an integer. If the conversion is possible, the CAST() function is used to convert the string to an integer. Otherwise, a default value or NULL is returned.

The following code snippet demonstrates this approach:

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

In this example, the first SELECT statement returns 100 because '100' can be converted to an integer. The second SELECT statement returns NULL because 'XXX' cannot be converted to an integer.

Considerations

It's important to note that the ISNUMERIC() function has some limitations. As pointed out by Fedor Hajdu, it may return true for strings containing certain characters, such as $ (currency), , (separators), and -. Therefore, it's recommended to use ISNUMERIC() with caution when validating input for integer conversions.

The above is the detailed content of How to Safely Cast Strings to Integers in T-SQL with Default Values or NULL?. For more information, please follow other related articles on the PHP Chinese website!