TRY_PARSE CONVERSION FUNCTION IN SQL SERVER 2012

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'

Result:
TRY_PARSE_CONVERSION_FUNCTION_IN_SQL_SERVER_2012

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

4 thoughts on “TRY_PARSE CONVERSION FUNCTION IN SQL SERVER 2012

Leave a Reply

Your email address will not be published.