Skip to content

String Manipulation — CAST and CONVERT

2005 April 2
tags: ,
by Shannon Lowder
colored leaf in towada

Though the color changes... it's still a leaf.

So far I’ve shown you just a few of the many string manipulation functions available in Microsoft SQL.  Today we’re adding two more of these tools to your tool belt.  CAST and CONVERT.  These two become useful when you’ve stored a value as one data type, and later find you need it used as another.  Please see the reference included in this article.  There is a nice chart showing you what data types can be converted, and what types they can become.

As usual, before getting into this too deeply, I want to include my standard disclaimer:

I would like to point out that you have to be careful when and how often you use the techniques below.  A good rule to keep in mind is text manipulation is slow and painful to a server.  If you can leave the text manipulation to your middle-ware or front end, that would be better.  But we all have been stuck in a situation where we needed to alter a string before those two points, and so I bring you the following lesson.

The Problem

Let’s discuss a couple problems you might face and how you can solve them using CAST or CONVERT.

You have a column in a table that counts the number of glasses of water consumed in a by users in a day.  You want to find out how many glasses have been consumed total. Simple think you, hmm?

The Solution

        SUM(glassesOfWater) AS [Total Glasses Consumed]
Total Glasses Consumed

Why are you getting .000?  When you look at the field, you notice it was set up as a numeric or DECIMAL(9,3) column.  But you only want to see the integer answer to the question of how many glasses have been consumed.  This is where you can use CAST or CONVERT.

        SUM( CAST(glassesOfWater AS int) ) AS [Total Glasses Consumed]


        SUM( CONVERT(INT, glassesOfWater) ) AS [Total Glasses Consumed]

Either way you will get the following result:

Total Glasses Consumed

CAST and CONVERT can become very useful.  How many times have you seen a table save a date as a VARCHAR?   How many times have you imported data into a temp table, with every column a CHAR or VARCHAR, then had to put those values into a final table?
Any of these cases could use CAST or CONVERT.  Just remember, these commands could put more load on your server than you think.  Always be aware of how your queries impact the performance of the server overall


5 Responses leave one →
  1. Scott permalink
    May 30, 2005

    This really helped and fixed the problem. Other web references had you doing a lot of manual things.

  2. Muhammed permalink
    May 31, 2005

    Totally informative blog post.I’ve bookmarked it already. Greets

  3. Sasha permalink
    May 31, 2005

    Maybe you should consider adding additional videos or charts to your weblog posts to make the visitors more entertained? I mean I just read through the entire post and it has been really great but since I’m more of a visual learner some more vids and charts would help a lot. Nevertheless the topic here is really worth pondering about. Thanks again.

Trackbacks and Pingbacks

  1. SQL201-The Syllabus | Shannon Lowder
  2. Microsoft Exam 70-433 (part 3) | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS