Solved

Get numeric value of string for order by

Posted on 2011-09-13
4
332 Views
Last Modified: 2012-05-12
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
Comment
Question by:theideabulb
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36533409
select cast(replace(column_name,'$','') as decimal(10,2) from....
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 36533421
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
 

Author Closing Comment

by:theideabulb
ID: 36533473
Nice job, i think it needed an extra right parentheses, but that definitely worked exactly the way i needed.  Thank you!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36533721
>>  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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Insert with SET how to handle join 6 80
[MYSQL]: Delete is very slow 4 95
Slow MySQL (InnoDB) Query with Inner Join & GroupBy 7 55
INDEX does not make a difference, why? 10 61
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question