How Can I Safely Cast NVARCHAR Strings to Integers in T-SQL, Handling Non-Numeric Values?

T-SQL Conversion: Casting Strings to Integers with Default Value or NULL Handling

In T-SQL, converting nvarchar strings to integers is crucial for data manipulation. However, when encountering non-numeric characters, such conversions can fail. This raises the need for handling conversion errors gracefully.

Solution

The CASE WHEN expression provides a robust mechanism to handle this scenario. The syntax is as follows:

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

Here's how it works:

• ISNUMERIC(): This function evaluates whether the nvarchar string @text represents a valid numeric value. If it returns 1 (true), the string is numeric.
• CAST(): If ISNUMERIC() returns true, the string is cast to an integer using CAST(). Otherwise, the result is set to NULL.

This approach ensures that if the conversion from string to integer succeeds, the correct integer value is returned. However, if the conversion fails due to non-numeric characters, a NULL value is returned.

Sample Code

DECLARE @text AS NVARCHAR(10)

-- Numeric string
SET @text = '100'
SELECT @text, CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END

-- Non-numeric string
SET @text = 'XXX'
SELECT @text, CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END

Limitations

While the ISNUMERIC() function offers a convenient way to check for numeric values, it's important to be aware of its limitations:

• It returns true for strings containing currency symbols ($), decimal separators (,), thousand separators (.), and arithmetic signs ( and -).
• It doesn't distinguish between integers and other numeric data types, such as decimals.

Despite these limitations, CASE WHEN with ISNUMERIC() and CAST() provides a practical solution for casting strings to integers and handling non-numeric cases in T-SQL.

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