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)
8 Comments. Leave new
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.
Thanks for sharing with nice SQL Server – Denali articles.
Thanks for bringing out the new functions in Denali.
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.
What are the main Difference between SQLSERVER2008 and Denali?
Thanks for explaining the difference between PARSE and CONVERT. It finally makes sense!
Well said. I agree.
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 :)