Solved

Get numeric value of string for order by

Posted on 2011-09-13
4
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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 …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

691 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