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

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 (http://blog.SQLAuthority.com)

23 thoughts on “SQL SERVER – Denali – Conversion Function – PARSE() – A Quick Introduction

  1. 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’).

    Like

  2. 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().

    Like

  3. Pingback: SQL SERVER – Denali – Conversion Function – TRY_CONVERT() – A Quick Introduction Journey to SQLAuthority

  4. Pingback: SQL SERVER – Denali – Conversion Function – TRY_PARSE() – A Quick Introduction Journey to SQLAuthority

  5. Pingback: SQL SERVER – Denali – Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT() Journey to SQLAuthority

  6. Pingback: SQL SERVER – Denali – 14 New Functions – A Quick Guide « Journey to SQLAuthority

  7. 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.

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

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