Reader Level:
ARTICLE

New Built in Functions in SQL Server 2012 : Conversion Functions

Posted by Santhosh Kumar Jayaraman Articles | SQL Server August 18, 2012
In this article, let us see how to use the 3 new builtin conversion functions of Microsoft SQL Server 2012 Release Candidate 0 with their syntax and sample queries.
  • 0
  • 0
  • 3492

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:

1.png

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:

2.png

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

3.png

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:

4.png

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:

5.png

And if you see in the message tab:

6.png

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:

7.png

And if you check in the messages tab:

8.png

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 passing 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:

9.png

And in the messages tab:

10.png

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')

11.png

12.png

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

COMMENT USING

Trending up