In SQL Server, there are several conversion functions available to convert data from one format to another. One of the new conversion functions introduced in SQL Server Denali is the TRY_PARSE() function. In this blog post, we will explore the TRY_PARSE() function and its benefits.

The TRY_PARSE() function is used to convert a string value to either a numeric or date/time format. If the string value cannot be converted, the function will return a NULL value instead of throwing an error. This is particularly useful when dealing with data that may contain invalid or unexpected values.

Unlike the PARSE() function, which relies on the Common Language Runtime (CLR) to perform the conversion, the TRY_PARSE() function does not require CLR installation on the server. This makes it a more versatile option for converting string values.

It’s important to note that the TRY_PARSE() function is specifically designed for converting string values to numeric or date/time formats. For other data type conversions, you can still use the traditional CAST or CONVERT functions.

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

Example 1: Converting String to INT
-- No error
SELECT PARSE('100.000' AS INT) AS ValueInt;
SELECT TRY_PARSE('100.000' AS INT) AS ValueInt;

-- Error
SELECT PARSE('A100.000' AS INT) AS ValueInt;
SELECT TRY_PARSE('A100.000' AS INT) AS ValueInt;

In the first set of examples, where the string can be converted to INT, both PARSE and TRY_PARSE functions work fine without throwing an error. However, in the second case where the string contains an alphabetic character, PARSE throws an error while TRY_PARSE returns a NULL result.

This distinct attribute of TRY_PARSE, where it returns a NULL result instead of an error, is particularly helpful when using the function over a table. Let’s see an example:

Example 2: Using TRY_PARSE with CASE statement
SELECT CASE WHEN TRY_PARSE('A100.000' AS INT) IS NULL THEN 'Error In Result' ELSE 'No Error' END AS ValueInt;

In the above example, we demonstrate how the CASE statement can be used with TRY_PARSE to handle any errors in the statement execution. This allows us to provide a custom message or handle the NULL result appropriately.

Now, let’s see how TRY_PARSE can be applied to a table:

Example 3: Converting String to INT from Table using TRY_PARSE
USE AdventureWorks2008R2;
SELECT SP.[StateProvinceCode], A.[PostalCode], TRY_PARSE(A.[PostalCode] AS INT) AS PostCode_TryParse
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. By using TRY_PARSE, we can convert the PostalCode values to INT. If any incorrect values are encountered, TRY_PARSE will return a NULL value instead of throwing an error.

As seen in the examples, the NULL value returned by TRY_PARSE can be used in conjunction with the CASE statement to display a desired message or handle the result appropriately.

In the next blog post, we will explore the usage of another conversion function called TRY_CONVERT() and discuss the differences between PARSE and TRY_PARSE.