Archive

Posts Tagged ‘try_parse’

SQL Server – "Denali" – Conversion Function – PARSE() and TRY_PARSE()

July 27, 2011 Leave a comment

PARSE() and TRY_PARSE() are new function introduced in SQL Server Denali.

PARSE() returns the result of an expression, translated to specified data type if the translation is possible, otherwise, it raises an error.

TRY_PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns NULL.

For example, let’s try to parse a numeric values as integer data type:

SELECTPARSE('10.20' AS INT) AS Result

SELECTTRY_PARSE('10.20' AS INT) AS Result

Result Set:

Result

———–

Msg 9819, Level 16, State 1, Line 1

Error converting string value '10.20' into data type int using culture ”.

 

Result

———–

NULL

 

(1 row(s) affected)

Since 10.20 is not an integer value PARSE() fails, and TRY_PARSE() returns NULL.

An instance of usefulness of TRY_PARSE() is when using PARSE() without error handling using TRY … CATCH.

DECLARE @NumList TABLE

(

       Num1 VARCHAR(5)

)

INSERT INTO @NumList VALUES ('10'),('10.20'),('11')

 

SELECTCASE WHEN TRY_PARSE(Num1 AS INT) IS NULL THEN 0

       ELSE PARSE(Num1 AS INT)

       END AS Result

FROM   @NumList

Result Set:

Result

———–

10

0

11

 

(3 row(s) affected)

Both these functions also support using different culture. For example, when parsing a string as DATETIME:

SELECTTRY_PARSE('27 juillet, 2011' AS DATETIME)

       AS 'English',

       TRY_PARSE('27 juillet, 2011' AS DATETIME USING 'fr-FR')

       AS 'French'

Result Set:

English                 French

———————– ———————–

NULL                    2011-07-27 00:00:00.000

 

(1 row(s) affected)

First result returns NULL, since 'juillet' is not a valid month according to us_english.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Categories: SQLServer, SQLServer 2012