How to Safely Cast NVARCHAR Strings to Integers in T-SQL and Handle Conversion Errors?

Casting Strings to Integers and Handling Conversion Failures

In T-SQL, we may encounter scenarios where we need to convert an nvarchar data type to an integer but handle cases where the conversion fails. One method to achieve this is through the combination of the CAST function and the ISNUMERIC() function.

Conversion with a Default Value

To cast an nvarchar string to an integer and return a default value if the conversion fails, we can use the CASE statement as follows:

SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE <default value> END

In this expression:

• @text is the nvarchar variable to be converted.
• ISNUMERIC(@text) = 1 checks if the string can be converted to an integer.
• If the conversion is successful, the CAST function converts @text to an integer.
• If the conversion fails, the specified default value is returned instead.

Example

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, the conversion is successful, and 100 is returned. In the second case, the string 'XXX' cannot be converted, and NULL is returned as the default value.

Handling ISNUMERIC() Limitations

It's worth noting that ISNUMERIC() may return true for some strings that are not valid integers, such as those containing currency symbols, decimal points, or /- signs. Therefore, it's advisable to validate the string more thoroughly before casting it.

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