Monday 9 July 2018

TRY_PARSE VS TRY_CONVERT


What are the differences between TRY_PARSE and TRY_CONVERT  [TRY_PARSE VS TRY_CONVERT] ?

TRY_PARSE
TRY_CONVERT
1
TRY_PARSE is a new feature in SQL Server 2012.

Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server.

Use TRY_PARSE only for converting from string to date/time and number types
TRY_CONVERT is a new feature in SQL Server 2012.

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null or ‘*’ or error in SQL Server.


Use TRY_CONVERT used for any general type conversion
2
SYNTAX:

TRY_PARSE ( string_value AS data_type [ USING culture ] ) 

string_value
nvarchar(4000) value representing the formatted value to parse into the specified data type.

string_value must be a valid representation of the requested data type, or TRY PARSE returns NULL.

data_type
Literal value representing the data type requested for the result.

culture
Optional string that identifies the culture in which string_value is formatted.

SELECT name FROM sys.syslanguages
-    Provides the culture string_value


SYNTAX:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type [ ( length ) ]
 The data type into which to cast expression.



expression
 The value to be cast.  value must be a valid representation of the requested data type, or TRY_ CONVERT returns NULL or ‘ERROR’ or ‘*’

length
An optional integer that specifies the length of the target data type. The default value is 30.


style
Optional integer expression that specifies how the TRY_CONVERT function is to translate expression, style accepts the same values as the style parameter of the CONVERT function. For more information, see CAST and CONVERT (Transact-SQL).



The range of acceptable values is determined by the value of data_type. If style is null, then TRY_CONVERT returns null.

3
Return Types
Returns the result of the expression, translated to the requested data type, or null if the cast fails.
Return Types
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null or error or ‘**’.
4
EXAMPLES

SELECT TRY_PARSE('1' AS INT) AS Result;  --> 1
OUTPUT:
1

SELECT TRY_PARSE('1' AS INT USING 'en-US') AS Result;  --> 1

OUTPUT:
1

SELECT 
    CASE WHEN TRY_PARSE('Govind' AS decimal USING 'sr-Latn-CS') IS NULL 
        THEN 'True' 
        ELSE 'False' 
END 
AS Result; --> True

OUTPUT:
True


SELECT 
    CASE WHEN TRY_PARSE('1' AS decimal USING 'sr-Latn-CS') IS NULL 
        THEN 'True' 
        ELSE 'False' 
END 
AS Result; --> False

OUTPUT:
False
SELECT IIF(TRY_PARSE('255' AS TINYINT) IS NULL, 'True', 'False') AS Result;  --> False

OUTPUT:
False
SELECT IIF(TRY_PARSE('256' AS TINYINT) IS NULL, 'True', 'False') AS Result;  --> True

OUTPUT:
True

SELECT TRY_PARSE('Govindaraj Kanniappan' AS datetime2 USING 'en-US') AS Result;  --> NULL

OUTPUT:
NULL

SELECT TRY_PARSE('Govindaraj Kanniappan' AS VARCHAR USING 'en-US') AS Result;  --> error, invalid data type varchar in function TRY_PARSE.

OUTPUT:
error, invalid data type varchar in function TRY_PARSE.


SELECT IIF(TRY_PARSE('01/01/2011' AS INT) IS NULL, 'True', 'False') AS Result;  --> True

OUTPUT:
True
SELECT IIF(TRY_PARSE('01/01/2011' AS datetime2) IS NULL, 'True', 'False') AS Result;  --> False

OUTPUT:
False

SELECT TRY_PARSE('Govindaraj Kanniappan' AS xml) AS Result;  --> Invalid data type xml in function TRY_PARSE.
OUTPUT:
Invalid data type xml in function TRY_PARSE.

SELECT TRY_PARSE('<root><parent>Govind</parent></root>' AS xml) AS Result;  --> Invalid data type xml in function TRY_PARSE.
OUTPUT:
Invalid data type xml in function TRY_PARSE.
EXAMPLES

SELECT TRY_CONVERT(INT, '1') AS Result;  --> 1
OUTPUT:
1


-- TRY_CONVERT
SELECT TRY_CONVERT(INT , NULL)  AS Result     --> NULL

OUTPUT:
NULL
SELECT TRY_CONVERT(INT , 99)  AS Result       --> 99

OUTPUT:
99
SELECT TRY_CONVERT(INT , 99, 1-10) AS Result  --> 99

OUTPUT:
99
SELECT TRY_CONVERT(INT , 'ABC') AS Result   --> NULL

OUTPUT:
NULL
SELECT TRY_CONVERT(SMALLINT, 'ABC') AS Result  --> NULL

OUTPUT:
NULL
SELECT TRY_CONVERT(TINYINT, 255) AS Result   --> 255

OUTPUT:
255
SELECT TRY_CONVERT(TINYINT, 256) AS Result   --> NULL

OUTPUT:
NULL
SELECT TRY_CONVERT(VARCHAR(1), 99) AS Result  --> '*'  It is not an error but the output is invalid

OUTPUT:
'*'  It is not an error but the output is invalid

SELECT TRY_CONVERT(VARCHAR(2), 99)  --> 99

OUTPUT:
99
SELECT TRY_CONVERT(xml, 4) AS Result;  --> 'error' 
OUTPUT:
Explicit conversion from data type int to xml is not allowed.








SELECT TRY_CONVERT(xml, ''Govindaraj Kanniappan') AS Result; --> Root 
OUTPUT:
Govindaraj Kanniappan


SELECT TRY_CONVERT(xml, '<root><parent>Govind</parent></root>') AS Result; --> <root><parent>Govind</parent></root> 
OUTPUT:
<root><parent>Govind</parent></root>

SELECT TRY_CONVERT(xml, '<root><Parent>Govind</parent></root>') AS Result; --> NULL 

OUTPUT:
NULL