TRY_CONVERT: Checking convertability in SQL Server 2012

Introduction

We may take a varchar input and to convert it to another data type before inserting/updating to tables. We mostly do an optimistic conversion expecting the varchar value will be in proper convertible format. But this approach can cause exceptions when the input is not in the expected format.

In SQL Server 2012 a new function has been introduced similar to TryParse in .NET through we can check the convertibility.

TRY_CONVERT

Let’s say we need to check a value can be converted to float:

SELECT TRY_CONVERT(float,'4.5')

Since  4.5 is convertible to float it will return a float with value of 4.5.

In below example the value is NA and is not convertible.

SELECT TRY_CONVERT(float,'NA')

The output will be NULL in this case.

By using TRY_CONVERT instead of direct CAST or CONVERT we can avoid conversion errors in the scripts.

Advertisements

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