TRY_CONVERT() and TRY_PARSE() for Data Conversions

It is common for a developer to have string data that needs to be parsed into a specific format.  However, it is even more common that not all of the data in the dataset is able to be converted to the specified format.  This leads to SQL Server throwing errors and queries failing unless those scenarios are taken into account – a tedious process of data cleansing.  In SQL Server 2012, some new keywords are available to help with converting data types.

Quick example:

select convert(int, 1)

select convert(int,'abc')

While the first statement will succeed, the second will throw an error.  You could handle this with some general error handling or with an ISNUMERIC(data) = 1, but those can present issues as well when working with some data sets that you need to cleanse.  See if the results to ISNUMERIC(‘2e5’) are what you would expect.  ISNUMERIC() appears to interpret this as 2 x 10^5, but conversion to an int does not.

Now, bump up your database compatibility to 110 (Compatibility level must be bumped up to 110 to use the new keywords).

select try_convert(int, 1)

select try_convert(int,'abc')

Aha!  The second statement returns NULL, indicating that the data isnt appropriate for your data conversion.  This information is quite useful when dealing with string data that can be of many formats.