SQL SERVER – Denali – Conversion Function – TRY_CONVERT() – 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_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 error
SELECT CONVERT(INT, '100') AS ValueInt;
SELECT TRY_CONVERT(INT, '100') AS ValueInt;
-- Error
SELECT 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.

SQL SERVER - Denali - Conversion Function - TRY_CONVERT() - A Quick Introduction tryconvert

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.

SQL SERVER - Denali - Conversion Function - TRY_CONVERT() - A Quick Introduction tryconvert1

SQL SERVER - Denali - Conversion Function - TRY_CONVERT() - A Quick Introduction tryconvert2

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.

SELECT
CASE WHEN TRY_CONVERT(INT, '100') IS NULL
THEN '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.

SQL SERVER - Denali - Conversion Function - TRY_CONVERT() - A Quick Introduction tryconvert3

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_TryConvert
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

SQL SERVER - Denali - Conversion Function - TRY_CONVERT() - A Quick Introduction tryconvert4

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.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Denali – Conversion Function – TRY_PARSE() – A Quick Introduction
Next Post
SQL SERVER – Denali – Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

Related Posts

10 Comments. Leave new

  • Varinder Sandhu
    September 8, 2011 7:25 am

    Last three post are really helpful as

    PARSE()
    TRY_PARSE()
    TRY_CONVERT()

    Many thanks for sharing.

    Reply
  • Partha Pratim Dinda
    September 8, 2011 10:13 am

    Thank you pinal .Your all posts are so helpful to me.

    Reply
  • Alot of thanks pinal sir,please given some some work

    Reply
  • Shatrughna Kumar
    September 8, 2011 11:23 am

    Slowly we are moving towards SQL Server Denali CTP3.
    You are making our learning curve always high.

    Reply
  • Really very helpful.
    Thanks :)

    Reply
  • Hi,
    This new function TRY_CONVERT() looks very helpful.
    But i´m using SQL Server 2008. There is a function similar to this (Without using TRY..CATCH)?

    Reply
    • No. You need to use try catch block to simulate try_convert() function

      Reply
      • I´m using TRY…CATCH inside a BEGIN TRANSACTION.. COMMIT TRANSACTION, and if one convert fails all work inside de transaction fails.
        Any solution for this? I tried SET XACT_ABORT OFF and didn´t worked.

      • Similar problem trying to simulate TRY_CONVERT in SQL 2008 R2 in a function

        TRY_CONVERT(date, ‘baddate’)

        I came up with

        Create Function dbo.udfParseDateWithDefault(
        @dateString as varchar(32),
        @defaultDate as Date
        ) Returns Date
        as
        begin

        Declare @returnDate Date;

        if(isdate(@dateString) = 1)
        set @returnDate = Convert(date, @dateString)
        else
        set @returnDate = convert(date, @defaultDate);

        return @returnDate
        end
        GO

        Declare @dateString varchar(32)
        set @dateString = ’13/12/2013′
        Set DateFormat dmy

        select dbo.udfParseDateWithDefault(@dateString, ‘1-Jan-2013′)

        returns –> 2013-01-01

        Declare @dateString varchar(32)
        set @dateString = ’13/02/2013’
        Set DateFormat dmy

        select dbo.udfParseDateWithDefault(@dateString, ‘1-Jan-2013’)

        returns –> 2013-02-13

  • Thanks lot….

    Reply

Leave a Reply

Menu