Introduction
SQL Server 2012 introduces three new built-in conversion functions: PARSE, TRY_ PARSE, and TRY_CONVERT. These functions were introduced to be more familiar to users of expression languages. They perform a subset of the functionality of the pre-existing conversion functions. This article will introduce these three new functions and their usage.
PARSE
The PARSE function allows for conversion of a string to numeric or date/time data types with cultural awareness. Any NVARCHAR(4000) string can be provided for the conversion, however if it is not appropriate for the destination data type, an error will be thrown.
A culture can be specified if needed; otherwise, the culture of the current session is used. Culture can be any of the .NET supported cultures, not being limited to those supported by SQL Server. However, if an incorrect culture is provided, the function will return an error. A list of the supported cultures and their codes can be found here.
The syntax for PARSE is:
PARSE ( string_value AS data_type [ USING culture ] )
An example of using this function would be to take a string representing payment in British pounds and convert it to the money datatype. In this case a culture is specified.
DECLARE @payment MONEY
SELECT @payment = PARSE('£3.89' AS MONEY USING 'en-GB')
SELECT @payment AS 'Result'
Result
-- -- -- -- -- -- -- -- -- -- -
3.89
(1 row(s) affected)
The same could be done with a date time string that the user input on the culturally aware front end. First we create a procedure (could also be a function) that accepts the datetime string and the current culture. The culture parameter would match the culture setting for the front end if it were written in .NET or used the same values as .NET.
CREATE PROCEDURE parse_datetime_sp (
@datetime NVARCHAR(4000),
@culture VARCHAR(10)) AS
BEGIN
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT PARSE(''' + @datetime + '''AS DATETIME USING ''' + @culture + ''')'
EXEC sp_executesql @sql
END
Now execute the stored procedure with different values to see how PARSE works with datetimes given a culture setting.
EXEC parse_datetime_sp '12/31/2012', 'en-US' Result: -- -- -- -- -- -- -- -- -- -- -- - 2012-12-31 00:00:00.000 (1 row(s) affected)
A proper conversion is made to the datetime format.
EXEC parse_datetime_sp '12/31/2012', 'fr-FR' Result: -- -- -- -- -- -- -- -- -- -- -- - Msg 9819, Level 16, State 1, Line 1
Error converting string value ’12/31/2012′ into data type datetime using culture ‘fr-FR’.
This time an error is thrown because the date is invalid for the specified cultures.
EXEC parse_datetime_sp '31/12/2012', 'fr-FR' Result: -- -- -- -- -- -- -- -- -- -- -- - 2012-12-31 00:00:00.000 (1 row(s) affected)
After fixing the date for the specified culture, the conversion works fine.
TRY_PARSE
TRY_PARSE, like PARSE, is used to convert a string value into the specified numeric or date/time data type with respect to a chosen culture (optional). If culture is not provided, the language of the current session is used. Below is the syntax for TRY_PARSE.
TRY_PARSE ( string_value AS data_type [ USING culture ] )
The difference between PARSE (explained above) and TRY_PARSE is that TRY_PARSE will return a null if the cast to the chosen data type is unsuccessful. Similar to PARSE, it requires the .NET Common Language Runtime (CLR) to be available on the server. It can only be used in SQL Server 2012 and above and will not be remoted to previous versions.
To build on the example used for PARSE, this example uses an incorrect format for payment received in pounds. This will cause the variable @payment to contain null because the conversion will fail.
DECLARE @payment MONEY
SELECT @payment = TRY_PARSE('£3-89' AS MONEY USING 'en-GB')
IF @payment IS NULL
SELECT 'Parse Failed' AS 'Result'
ELSE
SELECT @payment AS 'Result'
Result
-- -- -- -- -- -- -- -- -- --
Parse Failed
(1 row(s) affected)
The same conversion is performed below with a proper format for the money and with the language explicitly set for the session prior to the conversion.
SET LANGUAGE British
DECLARE @payment MONEY
SELECT @payment = TRY_PARSE('£3.89' AS MONEY)
IF @payment IS NULL
SELECT 'Parse Failed' AS 'Result'
ELSE
SELECT @payment AS 'Result'
Result
-- -- -- -- -- -- -- -- -- -- -
3.89
(1 row(s) affected)
Now execute the same stored procedure we created with PARSE to convert a date/time string to a datetime datatype using different values to see how TRY_PARSE works with datetimes given a culture setting.
EXEC parse_datetime_sp '12/31/2012', 'fr-FR' Result: -- -- -- -- -- -- -- -- -- -- -- - NULL (1 row(s) affected)
Instead of throwing an error when the conversion failed, TRY_PARSE returned a NULL.
EXEC parse_datetime_sp '31/12/2012', 'fr-FR' Result: -- -- -- -- -- -- -- -- -- -- -- - 2012-12-31 00:00:00.000 (1 row(s) affected)
The conversion is successful with the proper datetime format for the culture.
TRY_CONVERT
The new TRY_CONVERT function is very similar to the CONVERT function except that it returns NULL when the conversion cannot be completed, such as attempting to put alphabetic characters into a numeric data type. If the conversion cannot be completed because the data type of the expression is not allowed to be explicitly converted to the specified data type, an error will be thrown.
The format for the TRY_CONVERT is this:
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
Notice that you have the option of specifying a style as you could with the CONVERT function. You can refer to the topic for CAST and CONVERT for the list of style values as well as the matrix of allowed data type conversions.
An example of its use could be to try a conversion from a string input into a datetime data type where a NULL return means the user did not provide the proper input.
DECLARE @stringparm VARCHAR(30), @result VARCHAR(30) SET @stringparm = '2/30/2012' SELECT @result = CASE WHEN TRY_CONVERT (datetime, @stringparm) IS NULL THEN 'Bad Date' ELSE 'Good Date' END SELECT @result Result: -- -- -- -- -- -- -- -- -- -- Bad Date (1 row(s) affected)
Conclusion
The new conversion functions included with SQL Server 2012 provide string conversion capabilities resembling those found in expression-based languages. PARSE and TRY_PARSE rely on the .NET CLR being available. Use these two functions when you need culture aware conversions of string to numeric or date/time data types. TRY_PARSE and TRY_CONVERT are used to get null back when the conversion fails. The pre-existing conversion functions, CAST and CONVERT, are still available for use and do not rely on the .NET CLR.
For More Information
PARSE – http://msdn.microsoft.com/en-us/library/hh213316.aspx
TRY_CONVERT – http://msdn.microsoft.com/en-us/library/hh230993.aspx
TRY_PARSE – http://msdn.microsoft.com/en-us/library/hh213126.aspx
CAST and CONVERT – http://msdn.microsoft.com/en-us/library/hh213316.aspx
See all articles by Deanna Dicken
