Solved

Get numeric value of string for order by

Posted on 2011-09-13
4
330 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now