Retrieve results based on number in DB

Hey all

I have a DB with a price field it is (varchar)

how can I retrieve records that are less than ‘100.00’ as an example?

This is not going to work as you expect. Consider this:


create table money (val varchar(16));

insert into money values ('10.00');
insert into money values ('0.10');
insert into money values ('10.10');
insert into money values ('100.00');
insert into money values ('1,000.00');
insert into money values ('1000.10');
insert into money values ('9000.00');
insert into money values ('900.00');

select * from money order by val;

Gives you this:

0.1
1,000.00
10
10.1
100
1000.1
900
9000

So, even if you are not using locally formatted money values such as 1,000.00 you are still trying to force a varchar column to behave like an integer or float value.

If you do not control the database, then you will need to convert the strings to numbers in a temporary table maybe, using PHP somehow or even using JS.

In any case it going to be a) messy and b) unreliable.

Change the database table row to float or integer and sort the problem out at the source if you possibly can.

[edit]To answer your question though… using the test values I posted

select * from money where val < 100 order by val;

Gives:
00.10
1,000.00
10.00
10.10
[/edit]