String Manipulation -- CAST and CONVERT

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

SELECT

        SUM(glassesOfWater) AS [Total Glasses Consumed]

FROM drinking (READCOMMITTED)

Total Glasses Consumed

-----------------------------

13.000

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.

SELECT

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

FROM drinking (READCOMMITTED)

OR

SELECT

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

FROM drinking (READCOMMITTED)

Either way you will get the following result:

Total Glasses Consumed

-----------------------------

13

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
References

http://msdn.microsoft.com/en-us/library/ms187928.aspx