Page 1 of 1

Numeric BETWEEN AND

Posted: Thu Jan 20, 2011 11:08 am
by ssand
I have

Code: Select all

AND `price` BETWEEN 1 AND 2500
which works. But I need only data that is numeric and is BETWEEN 1 AND 2500.

I through I was on the right track with

Code: Select all

AND `price` regexp '[[:digit:]]' BETWEEN 1 AND 2500
but it returns nothing.

Is there a is_numeric() equivalent?

Thanks

Re: Numeric BETWEEN AND

Posted: Thu Jan 20, 2011 11:38 am
by ssand
Figured it out.

I found that [[:digit:]] included anything containing digits and need to use BETWEEN separately (thought I could just combine it).

Code: Select all

AND `price` regexp '^[0-9]+$'
AND `price` BETWEEN 1 AND 2500
Thanks

Re: Numeric BETWEEN AND

Posted: Thu Jan 20, 2011 1:05 pm
by AbraCadaver
Ideally your database column should be defined as the correct type like DECIMAL, NUMERIC, etc.

Re: Numeric BETWEEN AND

Posted: Thu Jan 20, 2011 1:28 pm
by ssand
Well, it was originally. Then someone thought they needed it to support more than just numbers and decimals. For a "price" column?!? Really?

Re: Numeric BETWEEN AND

Posted: Thu Jan 20, 2011 3:18 pm
by John Cartwright
ssand wrote:Well, it was originally. Then someone thought they needed it to support more than just numbers and decimals. For a "price" column?!? Really?
You seriously should consider eliminating or altering the data that does not conform to a decimal (2,4), fix your table structure, then write the correct query. What your doing is terribly inefficient.