Wednesday, August 20, 2014

Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

http://blog.sqlauthority.com/2011/09/09/sql-server-denali-conversion-function-difference-between-parse-try_parse-try_convert/

In SQL Server Denali, there are three new conversion functions being introduced, namely:
Today we will quickly look at PARSE() function. PARSE() function can convert any string value to Numeric or Date/Time format. If passed string value cannot be converted to Numeric or Date/Time format, it will result to an error. PARSE() function relies on Common Language Runtime (CLR) to convert the string value. If there is no CLR installed on the server, PARSE() function will return an error. Additionally, please note that PARSE only works for String Values to be converted to Numeric and Date/Time. If you need any other data type to be converted in to another data type, you may use traditional CAST or Convert Function. As a generic rule, there is always a bit performance overhead when any string value is parsed.
Now let us quickly look at examples on how PARSE() works:
Example1: Converting String to INT
SELECT PARSE('100.000' AS INT) AS ValueInt
This will return result as 100.
Example2: Converting String to Date/Time
SELECT PARSE('July 30, 2011' AS DATETIME)AS ValueDT
This will return result as 2011-07-30 00:00:00.000.
Example3: Converting String to INT from Table
You can clearly see that the PostalCode column in the database is in the format of the nvarchar. We can use PARSE to convert it to INT.
USE AdventureWorks2008R2
GO
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
Now the question is, “What happens if the string which we’d like to convert to INT cannot be converted to INT because it contains alphabets onto it? Well, when this happens, PARSE will throw an error.

SQL SERVER – Denali – Conversion Function – TRY_PARSE() – A Quick Introduction


Today we will quickly take a look at the TRY_PARSE() function. The TRY_PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.
The PARSE() function relies on Common Language Runtime (CLR) to convert the string value. If there is no CLR installed in the server, the TRY_PARSE() function will return an error. Additionally, please note that TRY_PARSE() only works for String Values to be converted to Numeric and Date/Time. If you need any other data type to be converted in to another data type, you may use the traditional CAST or the Convert Function. As a generic rule, there is always a bit improvement in the performance when any string value is parsed.
Now let us examine these examples showing how TRY_PARSE() works:
Example1: Converting String to INT
-- No errorSELECT PARSE('100.000' AS INT) AS ValueInt;SELECT TRY_PARSE('100.000' AS INT) AS ValueInt;
-- Error
SELECT PARSE('A100.000' AS INT) AS ValueInt;SELECT TRY_PARSE('A100.000' AS INT) AS ValueInt;
When you try first set where string can be converted to INT, it does not throw an error and it works fine.
However, in the second case wherein the string has an Alphabetic character, it cannot be converted to INT. In this case, PARSE will throw an error but TRY_PARSE will return a NULL result. Therefore, the major difference between PARSE and TRY_PARSE is that TRY_PARSE will TRY to PARSE and if PARSE fails it will produce a NULL result instead of an error.
This distinct attribute separating the two functions are very helpful when we are using TRY_PARSE function over table. This is displayed in the examples below:
Example2: Using TRY_PARSE with CASE statement.
SELECTCASE WHEN TRY_PARSE('A100.000' AS INT) IS NULLTHEN 'Error In Result'ELSE 'No Error'--TRY_PARSE('A100.000' AS INT)END AS ValueInt;
The example above demonstrates how CASE statement can be used with TRY_PARSE, while avoiding any errors in the statement execution.
In the following example, TRY_PARSE is applied over table and it makes it more meaningful:
Example3: Converting String to INT from Table using TRY_PARSE
You can clearly see that the PostalCode column in the database is in the format of the nvarchar. We can use PARSE to convert it to INT.
USE AdventureWorks2008R2
GO
SELECT SP.[StateProvinceCode], A.[PostalCode],TRY_PARSE(A.[PostalCode] AS INT) PostCode_TryParseFROM [Person].[Address] AINNER JOIN [Person].[StateProvince] SPON SP.StateProvinceID = A.StateProvinceID
GO
When attempting to PARSE a column, you might get one or more incorrect values. If the PARSE function is used it will throw an error and will not return any results but with TRY_PARSE it will not throw error but it will return the NULL value. As seen in example, the NULL value can be used along with CASE and desired message can be displayed.
In the next blog post, we will see the usage of TRY_CONVERT() and there we will see the real difference of PARSE  and TRY_PARSE.


SQL SERVER – Denali – Conversion Function – TRY_CONVERT() – A Quick Introduction


In SQL Server Denali, there are three new conversion functions being introduced, namely:
Today we will quickly take a look at the TRY_CONVERT() function. The TRY_CONVERT() function is very similar to CONVERT function which is avail in SQL Server already. Only difference is that it will attempt to CONVERT the datatype in specified datatype and while doing the same, if it fails (or error occurs) instead of displaying error it will return value NULL. Function CONVERT() is same as in earlier version (as far as I know till CTP3).
Now let us examine these examples showing how TRY_CONVERT() works:
Example1: Converting String to INT
-- No errorSELECT CONVERT(INT, '100') AS ValueInt;SELECT TRY_CONVERT(INT, '100') AS ValueInt;-- ErrorSELECT CONVERT(INT, 'A100.000') AS ValueInt;SELECT TRY_CONVERT(INT, 'A100.000') AS ValueInt;
When you try first set where string can be converted to INT, it does not throw an error and it works fine.
However, in the second case wherein the string has an Alphabetic character, it cannot be converted to INT. In this case, CONVERT will throw an error but TRY_CONVERT will return a NULL result. Therefore, the major difference between CONVERT and TRY_CONVERT is that TRY_CONVERT will TRY to CONVERT and if CONVERT fails it will produce a NULL result instead of an error.
This distinct attribute separating the two functions are very helpful when we are using TRY_PARSE function over table. This is displayed in the examples below:
Example2: Using TRY_CONVERT with CASE statement.
SELECTCASE WHEN TRY_CONVERT(INT, '100') IS NULLTHEN 'Error In Result'ELSE 'No Error'END AS ValueInt;
The example above demonstrates how CASE statement can be used with TRY_PARSE, while avoiding any errors in the statement execution.
In the following example, TRY_PARSE is applied over table and it makes it more meaningful:
Example3: Converting String to INT from Table using TRY_CONVERT
You can clearly see that the PostalCode column in the database is in the format of the nvarchar. We can use CONVERT to convert it to INT.
USE AdventureWorks2008R2
GO
SELECT SP.[StateProvinceCode], A.[PostalCode],TRY_CONVERT(INT, A.[PostalCode]) PC_TryConvertFROM [Person].[Address] AINNER JOIN [Person].[StateProvince] SPON SP.StateProvinceID = A.StateProvinceID
GO

When attempting to CONVERT a column, you might get one or more incorrect values. If the CONVERT function is used it will throw an error and will not return any results but with TRY_CONVERT it will not throw error but it will return the NULL value. As seen in example, the NULL value can be used along with CASE and desired message can be displayed.
In next blog post, we will discuss difference between CONVERT and PARSE as well TRY_CONVERT and TRY_PARSE. As well look at couple of interesting trivia questions.

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) PostCodeFROM [Person].[Address] AINNER JOIN [Person].[StateProvince] SPON SP.StateProvinceID = A.StateProvinceIDWHERE SP.[CountryRegionCode] = 'US'AND LEN(A.[PostalCode]) <=6
GO
SELECT SP.[StateProvinceCode],CONVERT(INT,A.[PostalCode]) PostCodeFROM [Person].[Address] AINNER JOIN [Person].[StateProvince] SPON SP.StateProvinceID = A.StateProvinceIDWHERE 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.

No comments:

Post a Comment