Get numeric value of string for order by

I have a varchar column of prices, and all the numbers are formatted like so.  I would like to order by this column from lowest to highest.  They all have the dollar sign when updated in the system.  How can i order them?  Is there a way to get them as a number and not a string?

Who is Participating?
johanntagleConnect With a Mentor Commented:
oops sorry forgot the order by part.  You will have to enclose it in a subselect

select a.price from (
  select cast(replace(column_name,'$','') as decimal(10,2) as price from table_name
) a
order by a.price

Adjust decimal(10,2) as needed, depending on the maximum possible value and number of decimal places
select cast(replace(column_name,'$','') as decimal(10,2) from....
theideabulbAuthor Commented:
Nice job, i think it needed an extra right parentheses, but that definitely worked exactly the way i needed.  Thank you!
>>  You will have to enclose it in a subselect

Btw: That shouldn't be necessary. You can ORDER BY an alias

SELECT cast(replace(price,'$','') as decimal(10,2)) as NumericPrice
FROM   yourTable
ORDER NumericPrice

... OR to sort only  ...

FROM   yourTable
ORDER cast(replace(price,'$','') as decimal(10,2)) as NumericPrice

Open in new window

If you're using the numeric value frequently, you might just throw it in a view.  If you can't change the column data type to numeric that is, (better option).

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.