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

Casting Strings to Integers with Default Values in T-SQL

In T-SQL, the need often arises to cast a string (nvarchar) value to an integer (int) data type. However, this conversion can fail if the string contains non-numeric characters. To handle such scenarios gracefully, it becomes necessary to return a default value or NULL if the conversion is unsuccessful.

To achieve this, the CASE expression, in conjunction with the ISNUMERIC() function, can be employed. ISNUMERIC() evaluates whether a string represents a valid numeric value, returning 1 if true and 0 if false.

Syntax:

CASE WHEN ISNUMERIC(@variable) = 1 THEN CAST(@variable AS INT) ELSE NULL END

Examples:

Consider the following example:

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 the first case, since '100' is a valid integer, the conversion succeeds, returning the value 100. In the second case, 'XXX' is not a numeric value, so the ISNUMERIC() function returns 0, causing the CASE expression to return NULL.

Considerations:

It's worth noting that ISNUMERIC() has certain limitations. For instance, it may return true for strings containing currency symbols ($), separators (,) or (.), and arithmetic operators ( , -). Therefore, additional validation may be necessary to ensure the integrity of the conversion.

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