-2

How to select from table where price is >= 3000 when the field type is string and the numbers are formatted as: 3 000, 20 000, 5 000...

'SELECT * FROM products WHERE price >= 3000'

Is there a way to format it on the fly to remove the space and keep the results output as the original?

chris85
  • 23,846
  • 7
  • 34
  • 51
medk
  • 9,233
  • 18
  • 57
  • 79
  • 6
    Firstly you should ask yourself why the field type is string at all if it's only going to contain numbers... – Mark Phillips Nov 03 '16 at 15:36
  • 1
    this will help: http://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql – devpro Nov 03 '16 at 15:38
  • @MarkPhillips it's just for readability, I know it's must be any type of INT – medk Nov 03 '16 at 15:39
  • 1
    @medk or a type of Numeric which would be better. http://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql – Mark Phillips Nov 03 '16 at 15:41
  • 1
    @medk You might want to do that in PHP, using [`number_format`](http://php.net/manual/en/function.number-format.php). – roberto06 Nov 03 '16 at 15:42

1 Answers1

4
SELECT * FROM `products` WHERE REPLACE(price, ' ', '') > 3000

But you should really change the type of your column.

roberto06
  • 3,844
  • 1
  • 18
  • 29