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. I find this is often quicker in SQL than in Excel Power BI.
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.
|
1 |
SELECT TRY_PARSE('100' as INT) AS OUTPUT |
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:
|
1 |
SELECT TRY_PARSE('XYZ' as INT) AS OUTPUT |
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.
|
1 2 3 4 5 |
SELECT CASE WHEN TRY_PARSE('XYZ' as INT) IS NULL THEN 'Sorry, conversion unsuccessful' ELSE 'Data converted successfully' END AS OUTPUT |
Again the TRY_PARSE statement is being used to convert ‘XYZ’ to number 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE DATABASE School GO USE School GO CREATE TABLE Students ( Id INT PRIMARY KEY IDENTITY, StudentName VARCHAR (50), StudentAge VARCHAR (50) ) GO INSERT INTO Students VALUES ('Sally', '25' ) INSERT INTO Students VALUES ('Edward', 'Fifty' ) INSERT INTO Students VALUES ('Jon', '30' ) INSERT INTO Students VALUES ('Scot', 'Thirty Five' ) INSERT INTO Students VALUES ('Ben', '37' ) |
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:
|
1 2 3 |
USE School SELECT StudentName, TRY_PARSE(StudentAge as INT) as AGE FROM Students |
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.
|
1 2 3 |
USE School SELECT StudentName, PARSE(StudentAge as INT) as AGE FROM Students |
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:
|
1 |
SELECT TRY_CONVERT(INT, '150') AS OUTPUT |
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.
|
1 |
SELECT TRY_CONVERT(INT, 'XYZ') AS OUTPUT |
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.
|
1 |
SELECT TRY_CONVERT(XML, 10) AS OUTPUT |
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:
|
1 2 3 4 5 |
SELECT CASE WHEN TRY_CONVERT(INT, 'XYZ') IS NULL THEN 'Data conversion unsuccessful' ELSE 'Data converted successfully' END AS OUTPUT |
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.
|
1 2 3 |
USE School SELECT StudentName, TRY_CONVERT(INT, StudentAge) as AGE FROM Students |
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.
|
1 2 3 |
USE School SELECT StudentName, PARSE(StudentAge as INT) as AGE FROM Students |
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
|
1 |
SELECT TRY_CONVERT(XML, '<car><name>Toyota</name></car>') AS Result |
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
|
1 |
SELECT TRY_PARSE('<car><name>Toyota</name></car>' AS XML) AS Result |
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.
References
- TRY_CONVERT (Transact – SQL)
- TRY_PARSE (Transact – SQL)
- SQL Server Data Type Conversion Methods and performance comparison
- Troubleshooting “TRY_CONVERT is not a recognized function”
- TRY_PARSE function in SQL Server 2012
Other great articles from Ben
| How SQL Server selects a deadlock victim |
| Identifying Object Dependencies in SQL Server Management Studio |
| Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions |
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021