01-02-2007, 09:14 PM #1Web Hosting Master
- Join Date
- Aug 2002
- Southwest Michigan
Treating an variable character as a string in MySQL
I've got a database filled with rows that contain a field of type varchar that is technically a number that has already been formated, ie 256,039.39
I would like to know how to treat this field as an integer for the purpose of doing a query such as
WHERE `price` > 256,039.38
to return the 256,039.39
As well, I need to LIST the results in ascending integer order, not ASCII. Any clues?
I cannot convert the formatted string to a straight integer so please do not suggest this. Thanks!!!
01-02-2007, 11:39 PM #2Retired Moderator
- Join Date
- Feb 2005
Do you like to make things difficult for yourself?
MySQL will treat strings as floating point numbers for the purposes of comparison, provided you take out the commas:
SELECT price, REPLACE(price, ',', '') AS real_price FROM table WHERE REPLACE(price, ',', '') > REPLACE('256,039.38', ',', '') ORDER BY real_price;Chris
"Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter