Tip

More on the practical applications of 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] 
  

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.