New Conversion Function in SQL Server 2012

Introduction

SQL Server 2012 introduced the four new conversion functions: TRY_CAST, TRY_CONVERT, PARSE and TRY_PARSE.

TRY_CAST

The TRY_CAST function returns a value cast to the specific data type if the cast operation is successful otherwise it returns NULL.

Syntax

TRY_CAST (Expression AS Data type(optional Length)

Argument / parameter

Expression: Any valid expression that to be cast.
Data Type: A Data Type of the expression to be cast. Here we can also specify an optional length of the target data type.

TRY_CAST takes the value and tries to convert it to the given data type. TRY_CAST returns the value to the specified data type if the cast is a successes otherwise it returns NULL. This means that if any error occurs during the casting this function then the null value is returned.

Example

--Simple TRY_CAST example

SELECT TRY_CAST('10.25' AS FLOAT)

--output

--10.25

-- TRY_CAST example with NULL value return

SELECT TRY_CAST ('This my test' AS INT) AS output;

--Output

-- NULL

SELECT CASE WHENTRY_CAST ('This my test' AS INT) IS NULL

THEN 'Cast is not valid'

ELSE 'Cast Valid'

END;

--Output

-- Cast is not Valid

The TRY_CAST function returns an error when the cast is not permitted explicitly.

--Simple TRY_CAST with error

SELECT TRY_CAST( 15.34 AS XML)

--output

--Explicit conversion from data type float to xml is not allowed.

TRY_CONVERT

The TRY_CONVERT function returns a value in the specified data type if the cast is done successfully otherwise this function returns NULL. The TRY_CONVERT function throws an exception if explicit conversion is not permitted.

Syntax

TRY_CONVERT (Data type (optional Length), Expression [, optional style])

Argument / parameter

Expression: Any valid expression to be cast.
Data Type: A Data Type of the expression to be cast. Here we can also specify an optional length of the target data type.
Style: Style is optional integer expression that specifies this function is to translate the given expression.

Example

--Simple TRY_CONVERT example

SELECT TRY_CONVERT(FLOAT , '10.25')

--output

--10.25

-- TRY_CONVERT example with NULL value return

SELECT TRY_CONVERT (INT , 'This my test') AS output;

--Output

-- NULL

SELECT CASE WHENTRY_CONVERT (INT, 'This my test') IS NULL

THEN 'Cast is not valid'

ELSE 'Cast Valid'

END;

--Output

-- Cast is not Valid

--Simple TRY_CONVERT with error

SELECT TRY_CONVERT(XML , 15.34)

--output

--Explicit conversion from data type float to xml is not allowed.

PARSE

The PARSE function translates an expression into a requested data type.

Syntax

PARSE ( string value AS data type [ USING culture ] )

Argument / parameter

String value: This is a nvarchar (4000) value representing the expression to be parsed into a specified data type. This must be a valid representation of the requested data type otherwise PARSE raises an error.
Data Type: Any valid data type supported by SQL Server and this is the result's data type.
Culture: Optional string value that specifies the culture in which the specified string value is formatted.

A culture argument is optional, if we are not providing the culture value then the language of the current session is used. The language can be set implicitly or explicitly (using a SET LANGUAGE statement). The culture argument accepts all cultures supported by the .NET Framework. It is not limited to the languages supported by SQL Server. If the culture argument is invalid then this function throws an error.

Use the PARSE function to convert a string value into a data/time or number types. For the other conversions use the CAST or CONVERT function. Note that the PARSE function depends on the .NET Framework Common Language Runtime (CLR).

Example

--Simple PARSE example with Date time

SELECT PARSE('Monday, 3 Octomber 2013' AS datetime2 USING 'en-US)

--output

-- 2013-10-03 00:00:00.0000000

-- PARSE example with currency symbol

SELECT PARSE('$128.34' AS FLOAT USING 'en-US)

--Output

-- 128.34

TRY_PARSE

The TRY_PARSE function is similar to the PARSE function except the TRY_PARSE function does not throw an error, if the conversion fails then this function returns the NULL value instead.

Syntax

TRY_PARSE ( string value AS data type [ USING culture ] )

Argument / parameter

All parameters are the same as for the PARSE function.

Example

--Simple PARSE example with Date time

SELECT TRY_PARSE('Monday, 3 Octomber 2013' AS datetime2 USING 'en-US)

--output

-- 2013-10-03 00:00:00.0000000

-- PARSE example with currency symbol

SELECT TRY_PARSE('$128.34' AS FLOAT USING 'en-US)

--Output

-- 128.34

SELECT TRY_PARSE('Test Data' AS datetime2 USING 'en-US)

--output

-- NULL