Duetschpire Duetschpire - 12 days ago 5
MySQL Question

Mysql query like number greater than x

I have a field for comments used to store the title of the item sold on the site as well as the bid number (bid_id). Unfortunately, the bid_id is not stored on its own in that table.

I want to query items that have a number (the bid_id) greater than 4,000 for example.

So, what I have is:

select * from mysql_table_name where comment like '< 4000'


I know this won't work, but I need something similar that works.

Thanks a lot!

Answer

Just get your bid_id column cleaned up. Then index is.

create table `prior`
(   id int auto_increment primary key,
    comments text not null
);
insert `prior` (comments) values ('asdfasdf adfas d d 93827363'),('mouse cat 12345678');
alter table `prior` add column bid_id int; -- add a nullable int column
select * from `prior`; -- bid_id is null atm btw
update `prior` set bid_id=right(comments,8); -- this will auto-cast to an int
select * from `prior`; 
+----+-----------------------------+----------+
| id | comments                    | bid_id   |
+----+-----------------------------+----------+
|  1 | asdfasdf adfas d d 93827363 | 93827363 |
|  2 | mouse cat 12345678          | 12345678 |
+----+-----------------------------+----------+

Create the index:

CREATE INDEX `idxBidId` ON `prior` (bid_id); -- or unique index