Click here to Skip to main content
11,479,841 members (47,459 online)
Click here to Skip to main content

Tagged as

Checking convertability in SQL Server

, 18 Sep 2013 CPOL 3.9K 1
Rate this:
Please Sign up or sign in to vote.
Avoiding conversion errors in scripts using TRY_CONVERT.

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.

Read http://technet.microsoft.com/en-us/library/hh230993 for details.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Saral S Stalin
Technical Lead
India India
No Biography provided

Comments and Discussions

 
GeneralMessage Automatically Removed Pin
23-Dec-13 23:00
memberNeo_parimal23-Dec-13 23:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 18 Sep 2013
Article Copyright 2013 by Saral S Stalin
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid