SQL SERVER – Denali – Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

In SQL Server Denali, three new conversion function have been introduced, namely,

PARSE()
TRY_PARSE()
TRY_CONVERT()

Earlier, we learnt about above three functions in various blog posts. If you have missed it, I suggest that you read all the three following blog posts before continuing this blog post as that will give you a better understanding of the subject. I have so far received a few questions on this subject; so I have attempted to present this blog post in question and answer format.

Conversion Function

Q: What is the difference between PARSE() and TRY_PARSE() functions?
A: While attempting to PARSE any column, if you get one or more incorrect values, PARSE function will throw an error. However, if you use TRY_PARSE function, it will not throw an error, but will return the result as NULL.

Q: What is the difference between PARSE() and CONVERT() functions?
A: Both are quite different. The PARSE function tries to parse a string and returns the value that is parsed from that string, if it can. CONVERT function tries to convert the string to a specified datatype; if it cannot, it will return an error. If you are not sure of what these two statements mean, consider the following example where string ‘100.00’ is attempted to PARSE and CONVERT to INT.

SELECT PARSE('100.000' AS INT) AS PARINT;
SELECT CONVERT(INT, '100.000') AS CONINT;

Let us check the result of the same.

You can clearly see that CONVERT failed because for it to convert a string to a specified datatype, a value that matches the destination datatype is needed, whereas PARSE parsed the string and returned specified datatype-matched string where it can. If you hold the specified string value as ‘A100.00’ instead of ‘100.000,’ both of them would have returned as error as they could not understand how this can be converted to INT.

Additionally, PARSE uses CLR datatype under the hood and currently can only convert a string to INT and DATETIME, whereas CONVERT works with any compatible datatype as well as it can be used to format dates.

Q: I have disabled CLR using sp_configure; how can I still use the PARSE function?
A: If the user has disabled the CLR by using sp_configure, but a .net framework is installed on the server, the PARSE function continues to work since it is a system function and not a user-defined function.

Q: What is optimal PARSE or CONVERT?
A: They are different and have different usages. Try both; whichever gives you a correct answer is something that you would want to use.

Just for kicks, I ran the following query together and checked their STATISTICS IO and execution plan; I found both them to be equal.

USE AdventureWorks2008R2
GO
SET STATISTICS IO ON;
SELECT SP.[StateProvinceCode]
,PARSE(A.[PostalCode] AS INT) PostCode
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
WHERE SP.[CountryRegionCode] = 'US'
AND LEN(A.[PostalCode]) <=6
GO
SELECT SP.[StateProvinceCode]
,CONVERT(INT,A.[PostalCode]) PostCode
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
WHERE SP.[CountryRegionCode] = 'US'
AND LEN(A.[PostalCode]) <=6
GO

The output of STATISTICS IO is as following: In both cases, the same amount IO reads are happening.

(8666 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvince'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8666 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvince'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Let us check the execution plan; it looks same as well.

Let me know if you have any other question about Conversion Function; I will expand this article appropriately.

Reference: Pinal Dave (https://blog.sqlauthority.com)