Ben Richardson

Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions

May 16, 2018 by

Data conversion is one of the most fundamental tasks of any programming language. Data received from different sources is often not in the right format. For example, if you receive an XML file where age is in the string format and you want to calculate an average age for the people in the file you will need to convert age into an integer.

To make the conversion process simple, the TRY_PARSE and TRY_CONVERT functions were introduced in SQL Server 2012. Before TRY_PARSE and TRY_CONVERT, SQL Server only had the PARSE and CONVERT functions.

In this article, we will look at what the TRY_PARSE and TRY_CONVERT functions are; how they differ from the PARSE and CONVERT functions, and how they differ from each other.

TRY_PARSE

The TRY_Parse function is used to convert string data into numeric or date data types. It returns NULL if the conversion is not possible. Let’s see an example.

In the above script, the TRY_PARSE function will attempt to convert the string ‘100’ into the integer hundred. Since this conversion is possible (because 100 can be converted into an integer) the OUTPUT will be the integer 100 as shown below:

Now let’s try to convert the string ‘XYZ’ into an integer using TRY_PARSE. Clearly, XYZ is not an integer. In this case, TRY_PARSE will return null. Try the following script:

The output will look like this:

You can use CASE or IIF statements to handle scenarios where conversion cannot be achieved. You can display an appropriate message to the users letting them know whether the conversion was successful or not. The following script is an example of how this works.

Again the TRY_PARSE statement is being used to convert ‘XYZ’ to string however, this time the CASE statement has been used to check if the result of the conversion is NULL. If the result is NULL, then a message ‘’Sorry, conversion unsuccessful’’ is printed on the console. If conversion is successful and TRY_PARSE doesn’t return false, the statement ‘Data converted successfully’ is shown in the console. In this case, TRY_PARSE returns NULL, therefore the output looks like this:

Difference between TRY_PARSE and PARSE

The basic difference between TRY_PARSE and PARSE is that the PARSE function returns an error when the conversion is not possible. The TRY_PARSE function doesn’t throw an error; it just returns NULL when conversion is not possible. This difference becomes very important when you work with tabular data.

Execute the following script to create dummy data that we are going to use to explain the difference between TRY_PARSE and PARSE functionality.

In the script above a database “School” has been created with one table “Students” in it. The Students table has three columns Id, StudentName and StudentAge. The type of StudentAge column has been set to VARCHAR since we want to convert this column into integers using both PARSE and TRY_PARSE. Finally, five records have been inserted into this table. You can see that the 1st, 3rd and 5th record has age in text, while 2nd and 4th records has age in numbers.

Let’s first use TRY_PARSE to convert StudentAge column into integer. Execute the following script:

The output of the above script looks like this:

You can see from the output, that TRY_PARSE returned NULL for the StudentAge column of those records where it could not convert the values to integer.

Now, let’s use PARSE function to see what it returns.

This script throws an error because in the second record we have “Fifty” in the StudentAge column and the PARSE function throws an error when it cannot convert a string into an integer. The error will look like this:

TRY_CONVERT

The TRY_PARSE function can only convert strings to numeric or date data types. The TRY_CONVERT function can perform conversions between all the data types except those where conversion is explicitly not permitted.

Like the TRY_PARSE function, TRY_CONVERT returns NULL when conversion is not possible. However, TRY_CONVERT throws an error when we try to perform a conversion that is not explicitly permitted.

Let’s start with a very simple example of TRY_CONVERT:

In the script above we simply tried to convert the string ‘150’ into an integer. This conversion was successful.

Now let’s try to convert ‘XYZ’ into an integer. We know that XYZ cannot be converted into integer; therefore, in this case, TRY_CONVERT will return an error.

The above script returns NULL

As mentioned above, there are some conversions that the TRY_CONVERT function cannot perform since they are not permitted. For example, converting an integer into XML is not permitted. In these cases, the TRY_CONVERT function returns an error.

Take a look at the following example.

Here, in this case, we are trying to convert 10 which is an integer into XML data type. Since this conversion is not permitted, an error will be thrown which looks like this:

You can use the CASE statement with the TRY_CONVERT function as well, in order to display an appropriate message to the user if the conversion fails. Have a look at the script below:

The output of the script above looks like this:

If you replace ‘XYZ’ in the above script with ‘50’, the conversion will be successful and you will see the message ‘Data converted successfully’ in the OUTPUT.

Difference between TRY_CONVERT and CONVERT

The difference between TRY_CONVERT and CONVERT is similar to the difference between TRY_PARSE and PARSE.

The TRY_CONVERT function returns NULL if the conversion is not possible while the CONVERT throws an error.

Let’s see the difference with help of the School database and the Students table that we created earlier.

Execute the following script to see what TRY_COVERT function returns when you convert the StudentAge column into integer.

The output of the above script looks like this:

You can see from the output, that TRY_CONVERT returned NULL for the StudentAge column of those records where it could not convert the values into an integer.

Now, let’s use CONVERT function to see what it returns.

This script throws an error because in the second record we have “Fifty” in the StudentAge column and PARSE function throws an error when it cannot convert a string into an integer. The error will look like this:

Difference between TRY_CONVERT and TRY_PARSE

The difference between TRY_CONVERT and TRY_PARSE is simple. TRY_PARSE can only convert string data type to numeric or date data types while TRY_CONVERT can be used for any general type conversion. Let’s explain this with the help of an example.

Let’s try to convert a string into XML data type using both the TRY_CONVERT and the TRY_PARSE functions.

Conversion using TRY_CONVERT

The output will look like this:

This means that the conversion was successful.

Now let’s perform the same conversion using TRY_PARSE.

Conversion using TRY_PARSE

Since TRY_PARSE can only convert string data into numeric or date data types, the above script will throw an error that looks like this:

A final more technical difference between TRY_PARSE and TRY_CONVERT is that the former depends on the .NET Framework Common Language Runtime (CLR) for its execution while the latter doesn’t.

See more

Automatically fill in SQL keywords and names with ApexSQL Complete, a free T-SQL complete add-in

References



Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
429 Views