Home > SQLServer, SQLServer 2012 > SQL Server – "Denali" – Conversion Function – PARSE() and TRY_PARSE()

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

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

Advertisements
Categories: SQLServer, SQLServer 2012
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: