What is T-SQL includes functions to help you explicitly convert between data types

CAST and TRY_CAST

The CAST function converts a value to a specified data type if the value is compatible with the target data type. An error will be returned if incompatible.

For example, the following query uses CAST to convert the integer values in the ProductID column to varchar values (with a maximum of 4 characters) in order to concatenate them with another character-based value:

SQLCopy

SELECT CAST(ProductID AS varchar(4)) + ': ' + Name AS ProductName
FROM Production.Product;

Possible result from this query might look something like this:

ProductName

680: HL Road Frame – Black, 58

706: HL Road Frame – Red, 58

707: Sport-100 Helmet, Red

708: Sport-100 Helmet, Black

…

However, let’s suppose the Size column in the Production.Product table is a nvarchar (variable length, Unicode text data) column that contains some numeric sizes (like 58) and some text-based sizes (like “S”, “M”, or “L”). The following query tries to convert values from this column to an integer data type:

SQLCopy

SELECT CAST(Size AS integer) As NumericSize
FROM Production.Product;

This query results in the following error message:

Error: Conversion failed when converting the nvarchar value ‘M’ to data type int.

Given that at least some of the values in the column are numeric, you might want to convert those values and ignore the others. You can use the TRY_CAST function to convert data types.

SQLCopy

SELECT TRY_CAST(Size AS integer) As NumericSize
FROM Production.Product;

The results this time look might like this:

NumericSize

58

58

NULL

NULL

…

The values that can be converted to a numeric data type are returned as decimal values, and the incompatible values are returned as NULL, which is used to indicate that a value is unknown.