SQL : find number in a range

[ghobbit]

Hi

Is it possible to run a SQL query to find a number in a range?

E.g.

I would like to have two fields one field called Reqno and the other barcode and filled as
Reqno Barcode
A1000-A1500 C532101
A1501-A2000 C532102
A2001-A2500 C532103

What I would like is for someone to type in on a form 'A1234' and have the 'C532101' returned as the result. The form etc I can work out and passing the search to the query I can also do, but I'm not sure of the syntax to find a number in a range or even whether this is possible.

Currently my Reqno field runs like this

Reqno Barcode
A1501 C532101
A1502 C532101
A1503 C532101
A1504.. C532101.. and so on

and this works and I can write a query etc to find the barcode I need but it would make everything so much tidier if I could simply have a field with a range and then have some sql to realise its a range and extract the barcode accordingly

many thanks

steve

 

[r937]

where 'A1234' 
  between substring(Reqno,1,5)
      and substring(Reqno,6,5)

works, but will be slow


better would be two separate columns, with a compound index on (Reqno_lo,Reqno_hi)

where 'A1234' 
  between Reqno_lo
      and Reqno_hi

r937.com | rudy.ca

 

[ghobbit]

Cool - many thanks

Steve