Ordering data type varchar

Question

I keep forgetting on how to order a column with datatype varchar

One of my column for pricing has these values: 5,12,15,18,21,23,31 etc when I cfoutput my query result I got the list showing as: 12,15,18,21,23,31,5

It should be from less price to higher prices: 5,12,15,18,21,23,31 but I can't make this happening because the datatype for this column is varchar.

I know there is a way around but I can't remember, can someone help me?

My query is real simple:

select ranges, prices

from fees

order by prices and this doesn't work

Carl Von Stetten · Answer

You don't specify what database system you are on. Assuming SQL Server, how about:

order by CAST(prices AS decimal(8,2))

That assumes you might have prices that are not whole units of currency (up to six digits to the left of the decimal and two to the right). You might need to adjust the scale and precision to your needs. If the prices are always whole units of currency, then you could replace "decimal(8,2)" with "int".

-Carl V.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded