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

In SQL Server Denali, three new conversion functions 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 better understanding of the subject. I have so far received few questions on this subject; so I have attempted to present this blog post in question and answer format.

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 error but will return the result as NULL.

Q: What is the difference between PARSE() and CONVERT() functions?
A: Both are quite different. 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 by 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 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 had 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 PARSE function?
A: If the user has disabled the CLR by using sp_configure, but .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 case, 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; I will expand this article appropriately.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Denali – Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

  1. Many many thanks to you for sharing your knowledge. It now clear to me.
    But i am not able to test this codes.
    I have sql server 2005 and .framework 3.5 and 4.0 both are install in my system but i don’t know how to activate clr on sqlserver .Can you tell me the way how to attach clr with sqlserver.
    Thanks again for sharing these nice article.

  2. Glad to make certain that this site works lucky on my htc wildfire Google Andoid phone wireless reading device, everything I aim to perform is functional. Gratitude for keeping it up to date with the most up-to-date.

  3. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

  4. I can’t be thankful enough to you. You have all the information needed to know and learn about SQL. Earlier to find anything about SQL, I use to Google it…but now I use your site to search any topic I want…and voila!!! Its there…Exactly what I was looking for….Thanks a ton for providing information in much simpler language which can be easily understood by freshers like me. You are the most generous person I have come across in terms of knowledge sharing where such meaningful data is accessible for free :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s