In SQL Server, there are several conversion functions available that allow you to convert data from one datatype to another. In this article, we will focus on one of the newer conversion functions introduced in SQL Server Denali called TRY_CONVERT().

The TRY_CONVERT() function is similar to the CONVERT() function that has been available in SQL Server for some time. The main difference is that TRY_CONVERT() attempts to convert the specified datatype, and if it fails, instead of throwing an error, it returns a NULL value.

Let’s take a look at some examples to understand how TRY_CONVERT() works:

Example 1: Converting String to INT
-- No error
SELECT CONVERT(INT, '100') AS ValueInt;
SELECT TRY_CONVERT(INT, '100') AS ValueInt;

-- Error
SELECT CONVERT(INT, 'A100.000') AS ValueInt;
SELECT TRY_CONVERT(INT, 'A100.000') AS ValueInt;

In the first set of examples, we try to convert a string to an INT. When the string can be successfully converted, both CONVERT() and TRY_CONVERT() return the expected result. However, in the second case where the string contains an alphabetic character, CONVERT() throws an error, while TRY_CONVERT() returns a NULL value.

This distinction between CONVERT() and TRY_CONVERT() is particularly useful when working with the TRY_PARSE() function over a table. Let’s see an example:

Example 2: Using TRY_CONVERT() with CASE statement
SELECT CASE WHEN TRY_CONVERT(INT, '100') IS NULL THEN 'Error In Result' ELSE 'No Error' END AS ValueInt;

In this example, we demonstrate how the CASE statement can be used with TRY_CONVERT() to handle potential errors. By checking if the TRY_CONVERT() result is NULL, we can display a custom message instead of an error.

Now, let’s explore how TRY_CONVERT() can be applied to a table:

Example 3: Converting String to INT from Table using TRY_CONVERT()
SELECT SP.[StateProvinceCode], A.[PostalCode], TRY_CONVERT(INT, A.[PostalCode]) AS PC_TryConvert
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP ON SP.StateProvinceID = A.StateProvinceID;

In this example, we have a table with a PostalCode column stored as nvarchar. We can use TRY_CONVERT() to convert the PostalCode values to INT. If any conversion fails, TRY_CONVERT() will return a NULL value instead of throwing an error.

By using the NULL value along with the CASE statement, we can handle these conversion failures and display a desired message.

In the next blog post, we will discuss the differences between CONVERT() and PARSE(), as well as TRY_CONVERT() and TRY_PARSE(). We will also explore a couple of interesting trivia questions related to SQL Server conversion functions.