T-SQL PARSE Function

Recently I completed several posts on functions new to T-SQL in SQL Server 2012, IIF, CHOOSE, CONCAT, and FORMAT, and decided it was time to expand on this theme and introduce PARSE. PARSE is new to T-SQL and relies on the presence of the .NET framework Common Language Runtime (CLR). There are two required arguments and one optional argument that are passed into the function.

  1. String value
    1. Value that is to be parsed to the defined data type
    2. Unicode variable character up to 4,000 characters
  2. Data type
    1. The data type that the string value is to be converted to
    2. See below for supported data types
  3. Culture
    1. Optional argument that identifies the culture the string is formatted in
  4. See below for culture settings

The data types that are supported to parse a value to are limited to only numeric and data and time, a full list of supported data types and cultures are outlined on MSDN here.

The easiest way to understand the way PARSE works is to begin working with it. Take a look at some of the examples and results below:

SELECT PARSE(‘$10.14’ AS MONEY USING ‘en-US’);
GO
Results 10.14

SELECT PARSE(‘€345,98’ AS money USING ‘de-DE’);
GO
Results 345.98
SELECT PARSE(FORMAT(GETDATE(), ‘D’, ‘de-DE’) AS DATETIME2 USING ‘de-DE’);
GO
Results 2013-01-16 00:00:00.0000000
SELECT FORMAT(GETDATE(), ‘D’, ‘de-DE’);
GO
Results Mittwoch, 16. Januar 2013

The queries and results are straight forward and what would be expected until the last query which formats the current date and time in German and then uses PARSE to return the German data and time back to English.

So the question of “Why include another conversion function?  We already have CAST and CONVERT.” is usually the first question I will be asked when presenting PARSE as a conversion option.  The true power and flexibility that I see  with PARSE over CAST or CONVERT is the ability to convert a string to a numeric or date and time value without having to use string functions to manipulate the input value.  Consider working with date and time values from multiple countries.  Based on the region you might be forced to manipulate the positions of the day, month, year, etc. in order to be able to convert them to a defined local.  Also consider data that has been imported from outside sources, comma delimited, Excel, or other such sources, that represents currency.  If the value(s) are preceded with a regional currency marker, “$” or “€”, then you once again would be forced to standardize the input value before applying CAST or CONVERT.

PARSE does provide an excellent means of applying consistent conversion of character data to a numeric or date and time format with little manipulation, but there are several considerations that you must take into account.  The first is that since PARSE does require the CLR so additional overhead is required and it will not be remoted, as this might cause an error on the remote server.  The other consideration to keep in mind is how PARSE treats NULL values.  When a NULL value is passed  into parse in place of the string value an error will be raised UNLESS the NULL value is passed in through a parameter or variable assignment.  The below code sample displays how PARSE treats NULL values in both cases.

SELECT PARSE(NULL AS DATETIME);
GO
Results
Msg 8116, Level 16, State 1, Line 1
Argument data type NULL is invalid for argument 1 of parse function.

DECLARE @value VARCHAR(10) = NULL
SELECT PARSE(@value AS DATETIME);
GO
Results NULL

This post will hopefully provide you with enough insight to begin using PARSE in your queries.

The above sample script can be downloaded here.

Leave a Reply


Hit Counter provided by Sign Holders