More on the practical applications of CONVERT and CAST

A further look at changing datatypes with CONVERT and CAST.

There are times when it becomes necessary or desirable to convert one datatype to another. A conversion can allow you to apply a function to your data that isn't available in the original datatype stored in your database. Consider for example the common situation where you store zip codes in a column as a character string.

Typically the VARCHAR or variable character field is used for this purpose. When you store zip codes in VARCHAR fields you can order the zip codes correctly, add a zip+4 entry and allow zip codes that are alphanumeric for international addresses. Addresses in the UK are typically of the type A1E B2G where the six characters can be either numbers or letters.

Suppose you want to perform arithmetic on your zip code data. You can't use arithmetic functions on character data, so you need to convert the ZIPCODE field to a numeric field. Since zip codes in the US are arranged in geographical sequence, simple addition and subtraction can locate zip codes that are close to one another. You might also want to work with zip code averages if you are creating census data, or for other purposes.

To convert a datatype to another you would use the CONVERT and CAST functions. Although they are similar, CAST is probably a better choice in terms of adhering to the ANSI SQL-92 standard and will port better to other databases. The two functions take the form:

       CONVERT (datatype [(length)], expression [ , style] 
       CAST (expression AS datatype)

Returning to the example of zip codes, here's how you would use CONVERT to perform the datatype conversion:

       SELECT SUM(CONVERT ( int , ZIPCODE)) from <tablename>

The expression above didn't use the style argument because it wasn't important to format the number field in any way in order to perform arithmetic functions. However, conversions of some fields such as date fields should really have a style argument, particularly if the calculations you perform require it.

Keep in mind that not all datatypes can be converted into other specific datatypes; there's no way to turn an IMAGE datatype into a CHAR datatype, for example. To see a chart of what data conversions are allowed, look in Microsoft SQL Server Books Online in the Extended Stored Procedures Programmer's Reference on the Data Types page.


Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.


 

This was first published in October 2004

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close