I have created a simple table with 4 fields:

id (auto incr. int)
prefix (varchar)
startrange (int) (these are ranges and this is starting value of the range)
endrange (int) ( and this is ending value of the range)

The data in it is like the following:

1, CL, 001, 499
2, AB, 000, 999
3, 66, 001, 999
4, 92, 201, 400
5, G, 1001, 5000
6, CC, 001, 2000

Now the above data has to be searched by a user:

So for eg. if user enters: 66121 it would show the record 3 coz i must search with and without prefix (i.e combine the prefix with start and end range).

But if user enters only: 121 then i have to show: 1, 2, 3, 6 (coz this is without prefix but comes in starting and ending range)

And user can also do: CL then it will show: 1 (I have taken care of this by stripping out the numbers from the user's search and then just searching the alphabets in prefix and show the matching results. So if user enters CL101 then i remove the 101 and just search prefix field for CL and show the results. This one is working fine.

The above 2 are not working for me which are i.e all data specified by user is numeric including or not including prefix...how to work it out ?