What are the differences between PARSE and TRY_PARSE [PARSE VS TRY_PARSE] ?
PARSE
|
TRY_PARSE
|
|
1
|
PARSE is a new feature in
SQL Server 2012.
Returns the result of
an expression, translated to the requested data type in SQL Server.
|
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
|
2
|
SYNTAX:
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 PARSE raises an error.
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
If the culture
argument is not provided, then the language of the current session is used.
This language is set either implicitly, or explicitly by using the SET
LANGUAGE statement. culture accepts any culture supported by the .NET
Framework; it is not limited to the languages explicitly supported by SQL
Server. If the culture argument is not valid, PARSE raises an error
|
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
|
3
|
Return Types
Returns the result of
the expression, translated to the requested data type.
|
Return Types
Returns the result of
the expression, translated to the requested data type, or null if the cast
fails.
|
4
|
EXAMPLES
SELECT PARSE('1' AS INT) AS Result; -->
1
OUTPUT:
1
SELECT PARSE('1' AS INT USING 'en-US') AS Result; --> 1
OUTPUT:
1
SELECT
CASE WHEN PARSE('Govind' AS decimal USING 'sr-Latn-CS') IS NULL
THEN 'True'
ELSE 'False'
END
AS Result; --> error
OUTPUT:
Error converting string value 'Govind' into data type
numeric using culture 'sr-Latn-CS'
SELECT
CASE WHEN PARSE('1' AS decimal USING 'sr-Latn-CS') IS NULL
THEN 'True'
ELSE 'False'
END
AS Result; --> False
OUTPUT:
False
SELECT IIF(PARSE('255' AS TINYINT) IS NULL, 'True', 'False') AS Result; --> False
OUTPUT:
False
SELECT
IIF(PARSE('256' AS TINYINT) IS NULL, 'True', 'False') AS Result; --> True
OUTPUT:
Error converting string value '256' into data type
tinyint using culture
SELECT PARSE('Govindaraj Kanniappan'
AS datetime2 USING 'en-US') AS Result; --> NULL
OUTPUT:
Error converting string value 'Govindaraj Kanniappan'
into data type datetime2 using culture 'en-US'.
SELECT PARSE('Govindaraj Kanniappan'
AS VARCHAR USING 'en-US') AS Result; --> error, invalid data type varchar in function PARSE.
OUTPUT:
Error, invalid data type varchar in function PARSE.
SELECT IIF(PARSE('01/01/2011' AS INT) IS NULL, 'True', 'False') AS Result; --> error
OUTPUT:
Error converting string value '01/01/2011' into data
type int using culture ''.
SELECT IIF(PARSE('01/01/2011' AS datetime2) IS NULL, 'True', 'False') AS Result; --> False
OUTPUT:
False
SELECT PARSE('Monday, 13 December 2010' AS date USING 'en-US') AS Result -->
2010-12-13
OUTPUT:
2010-12-13
SELECT PARSE('Monday, 13 December' AS date USING 'en-US') AS Result -->
error, due to invalid input
OUTPUT:
Error converting
string value 'Monday, 13 December' into data type date using culture 'en-US'.
SELECT PARSE('$345,98' AS money) AS Result --> 34598.00
OUTPUT:
34598.00
SELECT PARSE('$345,98' AS money USING 'en-US') AS Result --> 34598.00
OUTPUT:
34598.00
SELECT PARSE('€345,98' AS money USING 'en-US') AS Result --> error, invalid type money using culture 'en-US'
OUTPUT:
error, invalid type money using culture 'en-US
SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result --> 345.98
OUTPUT:
345.98
|
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
|
No comments:
Post a Comment