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