TRY_PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_PARSE function is Similar to PARSE function, but if PARSE function fails to convert the value throws an exception where as TRY_PARSE function returns a NULL value.
Important Note: TRY_PARSE function is not a native SQL SERVER function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.
Syntax: TRY_PARSE ( string_value AS data_type [ USING culture ] )
Parameter Details:
string_value : String expression which needs to be parsed.
data_type : Output data type, e.g. INT, NUMERIC, DATETIME etc.
culture : Optional string that identifies the culture in which string_value is formatted. If it is not specified, then it takes the language of the current session.
Now let us understand this TRY_PARSE function with examples:
-- TRY_PARSE String to INT SELECT TRY_PARSE('1000' AS INT) AS 'String to INT' -- TRY_PARSE String to NUMERIC SELECT TRY_PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to NUMERIC' -- TRY_PARSE String to DATETIME SELECT TRY_PARSE('05-18-2013' AS DATETIME) AS 'String to DATETIME' -- TRY_PARSE String to DateTime SELECT TRY_PARSE('2013/05/18' AS DATETIME) AS 'String to DATETIME' -- TRY_PARSE string value in the India date format to DATETIME SELECT TRY_PARSE('18-05-2013' AS DATETIME using 'en-in') AS 'String in the India date format to DATETIME' -- TRY_PARSE string value is in the US currency format to Money SELECT TRY_PARSE('$2500' AS MONEY using 'en-US') AS 'String in the US currency format to MONEY'
Difference between PARSE and TRY_PARSE
Try to convert invalid value, in this case PARSE throws exception but TRY_PARSE returns NULL Value
SELECT PARSE('Basavaraj' as DATETIME) 'PARSE RESULT' GO SELECT TRY_PARSE('Basavaraj' as DATETIME) 'TRY_PARSE RESULT'
Result:
PARSE RESULT
———————–
Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘Basavaraj’ into data type datetime using culture ”.
TRY_PARSE RESULT
———————–
NULL
Below example demonstrate how we can check the result of TRY_PARSE function in IF condition:
IF TRY_PARSE('Basavaraj' as DATETIME) IS NULL PRINT 'TRY_PARSE: Conversion Successful' ELSE PRINT 'TRY_PARSE: Conversion Unsuccessful'
Result:
TRY_PARSE: Conversion Successful
You may like to read the below new built-in function’s introduced in Sql Server 2012:
New Built in Functions introduced in Sql Server | |
CONVERSION FUNCTIONS | |
PARSE | TRY_PARSE |
TRY_CONVERT | |
STRING FUNCTIONS | |
CONCAT | FORMAT |
LOGICAL FUNCTIONS | |
CHOOSE | IIF |
DATE AND TIME FUNCTIONS | |
EOMONTH | |
DATEFROMPARTS | DATETIMEFROMPARTS |
SMALLDATETIMEFROMPARTS | DATETIME2FROMPARTS |
TIMEFROMPARTS | DATETIMEOFFSETFROMPARTS |
Using TRY_PARSE is it possible to change whole column value datatype