how to use between operator for string type of fields

Question

0.00/5 (No votes)

See more:

I have tabel fields like 'price varchar(30)'

In that field i have values like

price

10lakhs
20lakhs
13crores
15crores

now i want to retrieve the values using between operator based on the user choice.(if user enter minprice as 10lakhs and maxprice as 20lakhs or minpprice:13crores and maxprice:15crores from frontend side then i need to display the records between above prices).

SQL

price between '10lakhs' and '20lakhs' and ((price like '__ crores') or (pprice like '__ lakhs'))

It is displaying both lakhs and crores records.

Result:

SQL

10lakhs
20lakhs
13crores
15crores

Posted 31-May-13 1:21am

User-10031173

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-05-31T01:29:00

Rule one of data comparisons: always store numeric values in numeric fields.

When you compare two strings, it uses a string comparison, not a numeric one. This means each character is inspected one by one to see which is "higher" than the other. So a string comparison will be ordered as:

tect

"1"
"10"
"11"
...
"2"
"20"
...

and so forth.

Change your database. Store numbers in numeric fields, and dates in date based field. Only ever store string values in string fields or you will get loads of awkward, annoying errors which are hard to code round - and completely unnecessary.