Oracle text search for ranges

I'm looking for a better way of searching through numeric ranges in Oracle Text. I have a DB app that does a lot of GIS-type things, but we now want to add street range searching to it.

So I'd like to store the min and max values in a column, and search for a number within those values. I'm happy to go explore options, but I'd like some pointers on where to head. Does anyone have any suggestions for me?

EDIT: we're just trying to make address lookups easier. Text on the address parts has been a huge success, but w开发者_StackOverflow中文版e want to store street ranges instead of every individual house number. So, if I searched for "11 high street", I'd expect a match if high street had a range of 1 to 1000. I'd also like some options that I can use if I searched for "flat 1 11 high street" too though. I expect that I will have to do some jiggery with the input in these cases, I just want to know what kind of tools there are that I could try working with.

My suggestion is to make standard length string field for storing building numbers, create index on this field and then use between for search.

Something like this format:

NNNNNNCCCCBBBB

where:

NNNNNN - left-padded house number;
CCCC - left-padded character (like 'A' in '11A');
BBBB - left-padded building number

Under 'left-padded' I mean "filled with some symbol to standard length at left side", see for example result of select lpad('11',5,'X') from dual; query.

E.g. suppose, you have "11A high street building 5" address and choose '%' as filling symbol. When converted to proposed format it looks like '%%%11%%%A%%%' and 'high street' stored at separated field(s). Next is query example for selecting all houses between 1 and 1000:

with address_list as (
  select '%%%11%%%A%%%%' bnum from dual union all
  select '%1001%%%A%%%%' bnum from dual union all
  select '%%%%1%%%A%%%%' bnum from dual union all
  select '%%%%1%%%%%%%%' bnum from dual union all
  select '%%321%%%A%%%%' bnum from dual union all
  select '%1000%%%A%%%%' bnum from dual union all
  select '%1000%%QQ%%12' bnum from dual 
)
select * from address_list
where 
  -- from '1 high street'
  bnum >= '%%%%1%%%%%%%%' 
  and                    
  -- less then '1001 high street'            
  bnum < '%1001%%%%%%%%' 
order by 
  bnum

In real case is better to use chr(1) or any other unprintable symbol as symbol for padding.

Another thing is to build only function-based index for search without real field storage.

Anything wrong with

WHERE <number> BETWEEN minColumn AND maxColumn