• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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?

$199.90
$212.49
$239.79
$239.99
$249.88
$249.99
$24.90
$24.90
$59.89
$59.99
$183.30
$188.89
$189.99
$4.94
$4.95
$4.99
$5.49
$5.95
$5.99
0
theideabulb
Asked:
theideabulb
  • 2
1 Solution
 
johanntagleCommented:
select cast(replace(column_name,'$','') as decimal(10,2) from....
0
 
johanntagleCommented:
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
0
 
theideabulbAuthor Commented:
Nice job, i think it needed an extra right parentheses, but that definitely worked exactly the way i needed.  Thank you!
0
 
_agx_Commented:
>>  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  ...

SELECT Price
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).

0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now