music_lp90 Posted June 5, 2008 Share Posted June 5, 2008 Hi, I'm trying to figure a way to grab and sort some numbers that are stored in a MySQL database. The numbers have already been entered into the database in a varchar field and they are dollar values such as $37,950.00. Is there an SQL command that will ignore the "$", "," and "." and treat it as an integer, so I can sort it ascending or descending when I make the query? Thanks! Link to comment Share on other sites More sharing options...
justsomeguy Posted June 5, 2008 Share Posted June 5, 2008 You would need to take a substring of everything except the dollar sign, then convert that to a float. It would be better to store the amount in a float field without the currency sign, that's what most people do. Having to run a couple functions on each field when it's sorting is going to take the database much longer vs. just sorting a float field. Link to comment Share on other sites More sharing options...
jhecht Posted June 6, 2008 Share Posted June 6, 2008 What just some guy is saying: change the column type to float. You can add in the dollar sign yourself in the code. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.