Tuesday, September 6, 2011

New T-SQL Functions in SQL Server Denali Part 1 - Conversion Functions

It has been more than a month since i blogged.Feels good to be back in action !!!

There are around 22 new functions are introduced in Denali. In this series of posts let us take a look into functions category wise and what problem they intend to solve.

First let us take a look into the new Conversion functions

            PARSE
            TRY_PARSE
            TRY_CONVERT
 
PARSE

Parse is similar to Cast function which converts the data to required data type. But in Parse function we can include the culture in which it has to be converted.If no culture is provided , it will use the default culture. Parse function supports only converting the String values to  Numeric and Datetime Datatypes.

Syntax : PARSE(String_Value As Data_Type [USING Culture])

 

TRY_PARSE

Try_Parse function will check whether the parse function will be succesfull or not for the given string value , data type and culture.If the unable to parse the string value to the datatype the function will return NULL.Try_Parse takes the same parameters as like Parse function.This will be very usefull during data profiling to find out error records which cannot be parsed.

Syntax : TRY_PARSE(String_Value As Data_Type [USING Culture])

In the below example , the culture "en-US" returns value and "ar-SA" returns NULL as it is unable to parse the string into date with the arabian culture.

 

 
TRY_CONVERT

Try_Convert function will check whether the expression can be converted to the destination datatype or not. If unable to convert it returns NULL. This is similar to Try_Parse.But this checks the CONVERT function.This function has the same parameters as like CONVERT function.

Syntax : TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

In the below sample , when the conversion is succesfull it returns the converted value for string to date conversion. But while trying to convert string to int , it returns NULL as conversion cannot happen from string to int.

 

These are the three new conversion functions in Denali CTP3. Hope this post is helpfull and will continue to write on other function. The next post will be on the new function on Date and Time Category.
 

No comments:

Post a Comment