How to Safely Cast NVARCHAR to INT with a Default Value in T-SQL?

Casting Nvarchar to Integer with Default Value in T-SQL

In SQL Server, the challenge arises when attempting to convert non-numeric nvarchar data to integers. To handle such scenarios effectively, it is essential to specify a default value or NULL to return in the event of a failed conversion.

Fortunately, T-SQL offers a solution to this issue using a combination of the CAST and CASE statements. Let's explore how it works:

DECLARE @text AS NVARCHAR(10)

This line declares a variable named "@text" with a maximum length of 10 characters, allowing us to store nvarchar data.

SET @text = '100'

Next, the variable "@text" is assigned the value "100," which is a valid integer representation.

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

This line utilizes the CASE statement to evaluate whether the value of "@text" is numeric or not. If it returns 1 (True), the CAST function converts "@text" to an integer. If it's not numeric, the statement returns NULL.

The ISNUMERIC function plays a crucial role in this process. However, it's worth noting the limitations mentioned by Fedor Hajdu, such as its potential to treat strings containing symbols ($) or separators (.) as numeric. This is a crucial consideration when working with specific types of data.

In conclusion, using the CASE statement, in conjunction with CAST and ISNUMERIC, empowers developers to effortlessly convert nvarchar data to integers while handling potential conversion failures, ensuring the integrity and accuracy of their results in T-SQL.

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