SQL SERVER – Denali – Conversion Function – TRY_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 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 error
SELECT 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.

SELECT
CASE WHEN TRY_PARSE('A100.000' AS INT) IS NULL
THEN '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_TryParse
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON 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.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

19 thoughts on “SQL SERVER – Denali – Conversion Function – TRY_PARSE() – A Quick Introduction

  1. Does the TRY_PARSE implementation call DateTime.TryParse() under the hood to parse date values? If so, how does it reconcile that .Net’s DateTime range is larger than SQL Servers?

    And what format is the date expected to be in for TRY_PARSE

  2. Nice example ….

    it means

    TRY_PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns NULL on the other hand
    PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns error.

  3. I am sorry to post my query here. I am having Three tables like traks(ID,Name), ClientProject(ID,Name),TrackClientProject(Id,TrackID,ClientProjectID).I what to retrieve all clientproject in diffrent tracks. Its like matrix of Tracks Vs ClientProjects..
    Can any one help me?

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

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

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

  7. Pingback: SQL SERVER – Denali – Logical Function – IIF() – A Quick Introduction Journey to SQLAuthority

  8. Pingback: SQL SERVER – Denali – New Functions and Shorthand for CASE Statement Journey to SQLAuthority

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

  10. Pingback: SQL SERVER – Denali – New Functions and Shorthand for CASE Statement « Journey to SQLAuthority

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

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

  13. Any suggestions how to avoid the following? I’d expect TRY_PARSE() to give a NULL for dates prior to the year 1753 but instead it stops the execution with an error.

    SELECT TRY_PARSE(’01-Jan-0001′ AS datetime)

    Msg 6521, Level 16, State 1, Line 1
    A .NET Framework error occurred during statement execution:
    System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
    System.Data.SqlTypes.SqlTypeException:
    at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value)
    at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)
    at System.Data.SqlServer.Internal.CXVariantBase.DateTimeToSSDate(DateTime dt)

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