Let's learn Try_Convert function of SQL Server 2012 (Denali)

Introduction

Sql Server 2012 has brought a lot of new functions for the T-Sql developers. One among those functions is the Try_Convert function. In the earlier versions of SQL, it was not possible to know before hand if a casting is possible from one type to another unless we encounter some error confirming about the same.For example, let us try with the below statement

Select CONVERT(int,'This is a string and will fail if you try to convert to int')

A expected it failed with the below error message

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'This is a string and will fail if you try to convert to int' to data type int.

In order to avoid this kind of situation, we can use the Try_Convert function

Purpose

It transforms the source data into the target type if the conversion is possible else null.

Syntax

Try_Convert (Target Data Type, expression [ , style ])

Where,

Target Data Type=> The target datatype

Expression => The value to be cast

Style => It is optional integer expression. It indicates how the function will translate the expression.

Some Example

Select 
	Try_Convert(int, 'It is a string') As Result1
	,Try_Convert(int, '100') As Result2	
	,Try_Convert(Date,'18500412') As Result3
	,Try_Convert(DateTime,'Abcd efgh') As Result4
	
/* Result

Result1	Result2	Result3	Result4
NULL	100	1850-04-12	NULL

*/

Custom Try_Convert function

We can make our own Try_Convert function by using the CLR Function. Here is an attempt to do so

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
   [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

    public static SqlBoolean TryConvertFn(string value, string dataType)
    {
        bool status = TryConvert(value, dataType);
        return new SqlBoolean(status);
    }

    /// 

    /// Function: TryConvert  
    /// 
    /// 
    /// 
    /// bool
    private static bool TryConvert(string val, string key)
    { 
        bool flag = true;
	    
	    Dictionary dict = new Dictionary();
		dict.Add("int", typeof(Int32));
		dict.Add("bigint", typeof(Int64));
		dict.Add("datetime", typeof(DateTime));
		dict.Add("numeric", typeof(Decimal));

		try
		{
			switch (dict[key].FullName)
			{
				case "System.Int32":Int32.TryParse(val);break; 
				case "System.Int64":Int64.TryParse(val); break; 
				case "System.DateTime":DateTime.TryParse(val); break;
				case "System.Decimal":DateTime.TryParse(val);break;
			}
		}
		catch
		{
			flag = !flag;
		}
        return flag;     
    }
}

In this case we are maintaining a dictionary object where we are storing some datatypes (just for example sake). Then using the TryParse function, we are trying to convert the source to target type.

N.B.~I have assumed that the readers know atleast the basic of creating a CLR function and installation of the same

Conclusion

SQL Server 2012 (Denali) seems to be very mature and promising and has embeeded with many new functions.In this article we have looked into the Try_Convert function, the benefit of it and how can we make our own CLR based Try_Convert function.Hope the article will be useful.

Thanks for reading