SQL not returning result properly

Databases
1

Hi,

I have the following DB with data:

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `RngPrefix` varchar(10) NOT NULL,
  `StrRange` varchar(10) NOT NULL,
  `EndRange` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

INSERT INTO `products` (`id`, `RngPrefix`, `StrRange`, `EndRange`) VALUES
(1, '', 'AA001', 'AA999'),
(2, '', '2h0001', '2h3333'),
(3, '', '95001', '97000'),
(5, '', '2005N', '9999N'),
(6, '', '2003DN', '5003DN'),
(7, '', '3002LM', '9002LM'),
(8, '', '0A', '9Z'),
(9, '', '458A', '5124A'),
(10, '', 'CC001', 'CC2000'),
(11, '', '29220002', '292201000'),
(12, '', 'N001', 'N200');

And I am running the following query:

SELECT * FROM products WHERE 'CC456' >= StrRange AND 'CC456' <= EndRange

Why its not returning record # 10 ? And if you search for CC111 or CC1111 then it returns record 10. Why ?
The above database has ranges in it and i need to search between those ranges. What will be the best way ?

Thanks.

2

unfortunately you seem to expect them to behave as compound ranges consisting of an alphabetic portion and a numeric portion

CC456 does not fall between CC001 and CC2000, it actually comes after CC2000

that’s because the comparison is made on a character by character basis, from left to right

3

Hi,

Can you please suggest me how to design the db for this so the search is done perfectly. Please guide.

Thanks.

4

well, you could start by splitting your alphabetic and numeric parts into separate columns

but i’m not sure that’s going to be enough

5

I am already seperating the starting alphabets and ending ones into 2 fields called: Prefix and Postfix…
Then I in php side, fetched all the prefix and postfix and checked if the specified number has matching prefix / postfix.
If found then i was able to narrow down my search to that specific prefix/postfix. I guess this should work.

6

Hi,

I somehow managed to do it. Now only mixed ranges / series which cannot be seperated like: 0A to 9Z are the issue. How to deal with them ?

Thanks.

7

i think you’ll find they will work as is

8

Hi,

Ya done that too. Its showing some wrong records also but they are minimal and can be ignored easily.

Thanks.