Sql Server 2012 New Conversion Functions – Quick and Easy Examples

Do you need a quick and simple overview of the new SQL 2012 conversion functions?

This article will provide an overview of the new functions along with brief explanations and examples.

New conversion functions in Sql Server 2012

TRY_CONVERT

TRY_CONVERT is like the CONVERT function in SQL, with error handling.

If an error is encountered the function will return a null value.

Example of TRY_CONVERT() function


SELECT CONVERT(INT,'1-jan-2015')

--Conversion failed when converting the varchar value '1-jan-2015' to data type int.

SELECT TRY_CONVERT(INT,'1-jan-2015')

-- Output is NULL

PARSE

The SQL PARSE function is along similar lines to a CONVERT or CAST function.

It attempts to parse a string and return the parsed value. Additionally and unlike CONVERT or CAST there is also an optional “USING culture” parameter.

The culture parameter takes any culture that is supported by the .NET framework.

You can view the mappings of SQL languages mapped to culture here.

It is best to demonstrate with an example.

Example of the new PARSE() function


SELECT PARSE('50.00' as INT)

SELECT CONVERT(INT, '50.00')

SELECT PARSE('20-01-2015' as DATETIME using 'en-in') 
 AS 'String in Indian DateTime Format to DATETIME'

Notes:

  • PARSE can only parse values to either numeric or date/time datatypes
  • The PARSE function is built upon the CLR runtime
  • The PARSE function will do a best attempt to parse a string, vs the CONVERT function which requires the string input to be exact

e.g

PARSE will work with this date, and CONVERT will not ‘Saturday 8 jun-2015’

SELECT PARSE('Saturday 8 jun-2015' as DateTime)

TRY_PARSE

TRY_PARSE is the same as the PARSE function but includes error handling along.

This means that when a value is encountered that is unable to be passed, rather than throwing an error the function will return a null.

Example of the new TRY_PARSE() function


SELECT PARSE('A50.00' as INT)

SELECT TRY_PARSE('A50.00' as INT)

Using TRY_CONVERT and TRY_PARSE in the real world

It’s good to learn the theory and have an understanding of how these new functions work.

But how will you implement them? Please share with us how you will use these functions in your SQL Server environment.

Image credit

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.