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

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

Tags: ,

5 Responses to “String Manipulation — CAST and CONVERT”

  1. Scott 20050530 at 23:00 #

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

  2. Muhammed 20050531 at 01:00 #

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

  3. Sasha 20050531 at 02:00 #

    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/Pingbacks

  1. SQL201-The Syllabus | Shannon Lowder - 20110923

    […] cast and convert […]

  2. Microsoft Exam 70-433 (part 3) | Shannon Lowder - 20110927

    […] built-in scalar functions. This objective may include but is not limited to: CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX […]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.