MySQL and selecting numbers with commas

[mdavis79]

I'm having a little problem here...I have a large database that has a column with large numbers, that include commas in the numbers. What I want to do is execute a mysql selection that selects everything that is (as an example) less than 50000, however, when I do this, the numbers past the first comma are ignored. For example, if I have the number "50,125", that number will only get selected as "50" instead of the whole number. Is there a way to tell mysql to ignore the commas in the numbers? Or a way to correct this in php?

Thanks

[bubblenut]

Well, this is a design fault. The commas in numbers are aesthetic only. They shouldn't even be in the logic layer, they should be in the presentation layer and they deffinately shouldn't be in the content layer. If it is at all possible I suggest altering the database. This could be done with a script reading each row, striping the commas from the numbers and updating the row. You can then alter the table and change the column type to int.

HTH
Bubble

[slak]

Use MySQL's replace function.

SELECT * FROM yourTable WHERE REPLACE(ErrorsomeField, ',', '') > 1;

[mdavis79]

Thanks...I'll give that a try.