In SQL Server Denali, there are three new conversion functions being introduced, namely:
PARSE()
TRY_PARSE()
TRY_CONVERT()
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.
In the next blog post, we will see the usage of TRY_PARSE(), where we will try to answer the same question in detail.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
What does Parse() offer beyond what Cast/Convert do? The examples don’t seem to show any difference.
I had been hoping that eventually MS would offer a function similar to Oracle’s that lets me define the layout as a parameter. Example: Parse(‘May 21 11′,’MMM DD YY’).
Thanks for sharing Pinal
This is really new function that i learned in sql
waiting for next article on TRY_PARSE() and TRY_CONVERT()
HI Pinal ,
Nice post . Can u tell me what is the difference b/w old Cast /Convert to that of the new function Parse in Denali ?..
Hi pinal,
Thanks for sharing this concept.Can you elaborate your post that means where to use it and what is its advantage over cast/convert.
I will wait for your post regarding TRY_PARSE() and
TRY_CONVERT().
Thankyou Sir for this valuable post. Waiting for the post on next two functions.
I was not aware about this function in SQL.
Thanks for sharing with us.
Nice post.
Parse() is work same like Cast(). What is difference between these two functions?
It is same. Parse() is just like cast or convert.
Everybody is asking about the difference between Parse, Cast, and Convert. On a 2008 box, I get an error when trying to run a conversion of a string to an integer as shown below. Am I doing something wrong/missing something, or is everybody thinking of converting money to an integer, rather than a string to an integer?
The first segment works fine; the second does not. I believe the errors in the second segment are the target for the PARSE function, right?
DECLARE @money money, @varchar varchar(6), @int int
SELECT @money = ‘100.00’
SELECT @int = cast(@money as int)
SELECT @money, @int
SELECT @int = convert(int,@money)
SELECT @money, @int
DECLARE @money money, @varchar varchar(6), @int int
SELECT @varchar = ‘100.00’
–SELECT @int = cast(@varchar as int)
–Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value ‘100.00’ to data type int.
SELECT @int = convert(int,@varchar)
–Msg 245, Level 16, State 1, Line 5 Conversion failed when converting the varchar value ‘100.00’ to data type int.
Yes Mark! This is right situation where we need to switch to PARSE()
what are the prerequisites to use above function in sql server
The only thing is that you should be using Denali version
I don’t see any diff between Cast And Parse
I don’t see any diff between Cast And Parse.Please tell where to give it priority other then old cast functions.
Hi Pinal,
Thanks for posting the concept of PARSE() function. It is really needed in a situation where Mark Landry has mentioned.
Just tell me what’s Mark Landry