[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


String Manipulation -- CAST and CONVERT

Published on
9,045 Points
Last Modified:
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


Featured Post

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month