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 (https://blog.sqlauthority.com)

SQL Function, SQL Scripts
Previous Post
SQL SERVER – Download Denali CTP3 and Denali CTP 3 Product Guide
Next Post
SQL SERVER – A Guide to Integrating SQL Server with XML, C#, and PowerShell – Book Available for SQL Server Certification

Related Posts

16 Comments. Leave new

  • Rob Kellington (@kellington)
    September 6, 2011 7:39 am

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

    Reply
  • Varinder Sandhu
    September 6, 2011 8:14 am

    Thanks for sharing Pinal

    This is really new function that i learned in sql

    waiting for next article on TRY_PARSE() and TRY_CONVERT()

    Reply
  • 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 ?..

    Reply
  • Partha Pratim Dinda
    September 6, 2011 12:31 pm

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

    Reply
  • Thankyou Sir for this valuable post. Waiting for the post on next two functions.

    Reply
  • shatrughna kumar
    September 6, 2011 10:01 pm

    I was not aware about this function in SQL.
    Thanks for sharing with us.
    Nice post.

    Reply
  • Parse() is work same like Cast(). What is difference between these two functions?

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

    Reply
  • what are the prerequisites to use above function in sql server

    Reply
  • I don’t see any diff between Cast And Parse

    Reply
  • I don’t see any diff between Cast And Parse.Please tell where to give it priority other then old cast functions.

    Reply
  • Hi Pinal,

    Thanks for posting the concept of PARSE() function. It is really needed in a situation where Mark Landry has mentioned.

    Reply

Leave a Reply

Menu
Exit mobile version