T-SQL Tutorial

How to convert String to INT


In SQL Server, you can convert a string to an integer using the CAST or CONVERT functions. Both functions allow you to convert data from one data type to another. Here's an example of how to convert a string to an integer:

DECLARE @stringValue NVARCHAR(10) = '123';

-- Using CAST function
DECLARE @intValue1 INT;
SET @intValue1 = CAST(@stringValue AS INT);
SELECT @intValue1 AS ResultUsingCast;

-- Using CONVERT function
DECLARE @intValue2 INT;
SET @intValue2 = CONVERT(INT, @stringValue);
SELECT @intValue2 AS ResultUsingConvert;

In the above example, @stringValue is a variable containing the string representation of an integer ('123'). The CAST function and the CONVERT function are then used to convert this string to an integer.

It's important to note that if the string contains non-numeric characters, or if the conversion is not possible for any reason, you may encounter an error. To handle such scenarios more gracefully, you can use the TRY_CAST or TRY_CONVERT functions, which will return NULL instead of raising an error if the conversion fails. Here's an example using TRY_CAST:





DECLARE @stringValue NVARCHAR(10) = '123abc';

-- Using TRY_CAST function
DECLARE @intValue INT;
SET @intValue = TRY_CAST(@stringValue AS INT);

-- Check if conversion was successful
IF @intValue IS NOT NULL
SELECT @intValue AS ResultUsingTryCast;
ELSE
PRINT 'Conversion failed.';

In this example, the TRY_CAST function is used to attempt the conversion, and it returns NULL if the conversion cannot be performed successfully. This way, you can check whether the conversion was successful and handle it accordingly.