New Built in Functions in SQL Server 2012 : Conversion Functions

Microsoft SQL Server 2012 Release Candidate 0 has announced 3 new builtin conversion functions.

Those new functions are listed below.

1. Conversion functions
   • Parse
   • Try_Parse
   • Try_convert

Conversion Function

There are 3 new conversion functions and for those who have already worked in the .Net framework, they will easily figure out what the functions will do when I say parse, tryparse.

PARSE

This function will parse the value and return the result. If it is not able to parse then it will throw an error. You can use this function to convert strings or datetime data to datetime or numeric values. Please trust me, this function has performance issues compared to CAST/CONVERT. The syntax is:

PARSE ( string_value AS data_type [ USING culture ] )

This function expects the 3 parameters:

• String_value - The expression to be parsed
• Data_type - The data type we are converting to
• CULTURE - The culture i.e language such as gb-en or us-en. This is an optional parameter.

Let us see some examples to learn how it works.

SELECT PARSE('08-04-2012' AS datetime USING 'en-US') AS Date

select cast('08-04-2012' AS datetime) as Date

Now the output is:



So many people can wonder why we have to use Parse when it produces the same output as the CAST function.

Suppose you are not using the "en-US" culture and instead you are working in Paris and your server date settings is native to 'fr-FR' and you display the date in DD/MM/YYYY format, then what will happen if you use the CAST function?

See the following queries:

SELECT PARSE('08-04-2012' AS datetime USING 'fr-fr') AS Date

select cast('08-04-2012' AS datetime) as Date

Now the output will be:



So now you might understand the real use of Parse I guess. And this is not the only one.

In my database I save an inserted date as varchar and in the format "14-Aug-2012" like this. Then how will you convert it into a normal datetime? That's where the Parse function is relevant.

Consider my following queries and see the outputs.

SELECT PARSE('14-Aug-2012' AS datetime USING 'en-us') AS Date

SELECT PARSE('August 14,2012' AS datetime USING 'en-us') AS Date

Isn't it good?? Saves developer's time.

We have seen for datetime, now what about numeric? Ok let us see another example.

In many countries, in decimals, instead of a '.' a comma ',' is used, especially in European countries.

125.00 is the same as 125,00 in France.

So in the database, I have a varchar column but save values in decimals and have records like:

125,00
134,00
456,00

Now we have to go for culture options in the parse function.

select parse('125,00' as decimal using 'en-US')

select parse('125,00' as decimal USING 'fr-FR')

These queries will give me output as:



So the main advantage of the Parse function is to parse the expression for various cultures.

TRY_PARSE

It is similar to the Parse function but the only difference is when it is not able to parse, it will return NULL instead of throwing an error as from the Parse function. The syntax is:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

This function expects 3 parameters:

• String_value - The expression to be parsed

• Data_type - The data type we are converting to

• CULTURE - The culture i.e language such as gb-en or us-en. This is an optional parameter

Let us see some examples to understand how it works.

--try_parse demo

SELECT PARSE('13-04-2012' AS datetime USING 'en-us') AS Date

SELECT try_PARSE('13-04-2012' AS datetime USING 'en-us') AS Date

The output will be:



And if you see in the message tab:



Then that occurs when the parse function is not able to parse, it throws an error. But try_parse just returns null.

Now let us see another example. What will happen if I try to parse an alphanumeric string to integer?

select parse('df23' as int using 'en-US')

select try_parse('df34' as int USING 'en-US')

The output will be:



And if you check in the messages tab:



So try_parse avoids throwing an exception and returns null if it is unable to parse.

TRY_CONVERT

This function is similar to the existing Convert function but the difference is whenever it is not able to convert, it returns null.

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

This function expects 3 parameters:

• Data_type - The data type we are converting to

• Expression - The value to be converted/cast.

• Style - Integer parameter that specifies how the cast expression should be. This is an optional parameter

Let us see some examples to understand how it works.

SELECT CONVERT(datetime, '8/13/2012', 103) AS date

SELECT try_CONVERT(datetime, '8/13/2012', 103) AS date

Note here I am ing the month as 13, so the conversion will fail. The first statement will throw an error. But what will happen with the second?

See the output below:



And in the messages tab:



Now one of my varchar columns always contains an integer value. But by mistake I once saved an alphabetic character. So whenever I try to convert it to an integer using cast or convert it throws me an error. But I don't want an error. If there are no integer values, then it should return null.

See the following queries and the output:

select try_convert(int,'a')

select convert(int,'a')

So in general try_convert is similar to try_parse in that whenever it is not able to convert, it will return null.