String Manipulation — CAST and CONVERT

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


By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.


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

  2. 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.

Leave a comment

Your email address will not be published. Required fields are marked *