Casting: Not just for wizards and fishermen

When working with somebody else’s data, you don’t always have a choice in how things are structured. Storing numbers as varchars isn’t the ideal solution in a SQL database, but if that’s what gets delivered, that’s what you work with. Want to sort by the numerical value, not the alphabetical? Use the SQL cast() function! Pass it a field and a type, and you’re good to go.

The fun part, to me, is showing how to do it. I do most of my demos with the standard Northwind database most SQL users are familiar with. As far as I’m aware, its numbers are always stored as numbers. So first, I’m going to show the output sorted alphabetically with this command:

SELECT ProductID, Quantity FROM dbo.[Order Details] ORDER BY CAST(Quantity AS VARCHAR)

Our numbers are now alphabetical (i.e. 1, 2, 10 will be ordered as 1, 10, 2). Now I’ve successfully proven that I can do exactly what they don’t want. So now…

SELECT ProductID, Quantity FROM dbo.[Order Details] ORDER BY CAST(CAST(Quantity AS VARCHAR) AS INT)

And voila! We’ve used nested CAST() functions to order a SQL query exactly as it would have been ordered to begin with. But hey, you work with the data you have, not the data you want. What matters is that you get it done, right?

Enhanced by Zemanta

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