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

TRY_CAST function returns a value cast to the specific data type if cast operation done successfully otherwise it returns NULL.

Syntax

TRY_CAST (Expression AS Data type(optional Length)

Argument / parameter

• Expression - Expression is any valid expression that to be cast.
• Data Type - This is a Data Type which expression to be cast. Here we can also specify optional length of 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 cast is successes otherwise it return NULL. This means any error is occurs during the casting this function return null value.

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
TRY_CONVERT function returns a value in to given data type if cast is done successfully otherwise this function returns NULL. TRY_CONVERT function is threw exception if explicitly conversion is not permitted.
Syntax

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

• Expression:  Expression is any valid expression that to be cast.
• Data Type:  This is a Data Type which expression to be cast. Here we can also specify optional length of 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
PARSE function translates expression in to requested data type.
Syntax

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

• String value: this is nvarchar (4000) value representing the expression to be parsing in to 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 result's data type.
• Culture: Optional string value that specifies the culture in which given string value is formatted.

A culture argument is optional, if we are not providing the culture value then the language of 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 PARSE function for converting string value into data/time and number types. For the other conversions uses CAST or CONVERT function. Note that PARSE function is depending on .NET framework CLR (Common Language Runtime).

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

TRY_PARSE function is similar to PARSE function except TRY_PARSE function does not threw an error, if conversion is failed instead of this function return NULL value.

Syntax

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

Argument / parameter

All parameters are same as 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