Let's learn Try_Parse 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_Parse function.This function is there in C# for a long time and has been included under the Sql Server umbrella.It allows us to test whether something is parseable.Parse throws an exception if it cannot parse the value, whereas TryParse returns a bool indicating whether it succeeded.

Purpose

It basically identifies if the type specified is applicable for parsing or not and returns the appropriate status.

Syntax

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Where,

String_value => the value specified

Data_type = > the target type into which the string_value will be transformed

Culture => If any culture needs to be provided for the string_value to be formatted. This is optional and if not provided then the current session language will be use.

Some Example

Declare @t table(Data varchar(10))
Insert into @t values('12'),('a'),('.5'),('2011-22-07')
--Select * from @t

Select 
	Data
	,TryParseDateTime = Try_Parse(Data AS Date) 
	,TryParseDecimal = Try_Parse(Data AS Decimal) 
	,TryParseNumeric = Try_Parse(Data AS Numeric(20,10)) 
,TryParseWithConcat = Try_Parse(Concat(Data,'7') AS int using 'en-US')
from @t

/* Output

Data		TryParseDateTime	TryParseDecimal	TryParseNumeric	TryParseWithConcat
12			NULL			12	12.0000000000	127
a			NULL			NULL	NULL		NULL
.5			NULL			1	0.5000000000	NULL
2011-22-07		NULL			NULL	NULL		NULL

*/

Custom Try_Parse function

We can make our own Try_Parse 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 TryParseFn(string value, string dataType)
    {
        bool status = TryParse(value, dataType);
        return new SqlBoolean(status);
    }

    /// 

    /// Function: TryParse
    /// Purpose: Converts a string value to the target type. If succeeds returns true else false
    /// 
    /// 
    /// 
    /// bool
    private static bool TryParse(string val, string key)
    { 
           /* 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
            {
                TypeDescriptor.GetConverter(dict[key]).ConvertFromString(val);
                return true;
            }
            catch
            {
                return false;
            }*/
            
             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.Parse(val);break; 
	                    case "System.Int64":Int64.Parse(val); break; 
	                    case "System.DateTime":DateTime.Parse(val); break;
	                    case "System.Decimal":DateTime.Parse(val);break;
	                }
	            }
	            catch
	            {
	                flag = !flag;
	            }
        return flag;        
    }
}

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

N.B.~I have assumed that the readers know at least 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 embedded with many new functions.In this article we have looked into the Try_Parse function, the benefit of it and how can we make our own CLR based Try_Parse function.Hope the article will be useful.

Thanks for reading