how can i retrieve the records from table using between operator in mysql

Question

everybody

I want to retrieve the records from a table(price) based on the following query.My table is

+-----------+
| pprice    |
+-----------+
| 10 lakhs  |
| 11 lakhs  |
| 12 crores |
| 13 lakhs  |
| 15 crores |
| 16 lakhs  |
+-----------+

I'm using the following query

select * from price where pprice between '10 lakhs' and '20 lakhs';

but it returns the following result

+-----------+
| pprice    |
+-----------+
| 10 lakhs  |
| 11 lakhs  |
| 12 crores |
| 13 lakhs  |
| 15 crores |
| 16 lakhs  |
+-----------+

what's the problem in my query.please give me reply for this.

Between function doesn't work with `varchar` column type. You need to change your column type from `varchar` to any numerical column type. For that you also have to update all rows. — Himanshu, May 29 '13 at 11:49
This is inappropriate use of database. Numeric values such as price should be stored in pure numbers like 100000 (and not like 10 million or 10 lakhs). I recommend that instead of continuing like this, you should spend some time in updating all such values in the database by writing and executing some queries manually. — Aziz Shaikh, May 29 '13 at 11:52
@zerkms - Doesn't work means `BETWEEN` can't understand words. So it won't give you expected result. — Himanshu, May 29 '13 at 11:53
@hims056: it does support string types. Please bother looking at least at documentation next time you're arguing: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between — zerkms, May 29 '13 at 11:54

score 1 · Answer 1 · answered May 29 '13 at 12:12

1

If you're dealing with equal magnitudes, such as lakhs in your example, you can do something like this:

select *
  from price
 where pprice between '10 lakhs' and '20 lakhs'
   and pprice like '__ lakhs';

answered May 29 '13 at 12:12

RichardTheKiwi

105,798
26
196
262

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

When we ares comparing integers, we are comparing their numerical values ;

i.e. 10 > 6.

When we are comparing strings, we are using an alphabetical comparison ;

i.e. '10'< '6'

SEE HERE

edited May 23 '17 at 12:18

Community

1
1

answered May 29 '13 at 11:51

PSR

39,804
41
111
151

Please provide a definition for "will not work". It's syntactically correct. "It is for numbers." --- nope – zerkms May 29 '13 at 11:51

score 0 · Answer 3 · answered May 29 '13 at 11:56

What happens is, that your mySql is not aware about lakhs and crores and splits off the number part at the first space while auto-converting the formats. And then: The result is quite proper!

Besides the design pattern here (measure as part of the data field, normalization, ...) what is the relation between lakhs and crores ? How should mysql know that 1 crore is larger than 1 lakhs (ore vice cersa, I don't what this is).

how can i retrieve the records from table using between operator in mysql

3 Answers3