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
|
