=?iso-8859-1?q?Benjamin_S=F8lberg?= (benjamin.soelberg@gmail.com) wrote:

: Create an index on substr(startnumber, 1, 4), substr(endnumber, 1, 4),
: startnumber, endnumber

: Then do a SQL like this :
: SELECT *
: FROM numbers
: WHERE '3131' BETWEEN SUBSTR (startnumber, 1, 4) AND SUBSTR
: (endnumber, 1, 4)
: AND '31313131' BETWEEN startnumber AND endnumber;

: Is this correct ?

I can't say off hand. I think if you use a functional index then the
syntax used within the where must be identical to the syntax used in the
functional index.

The "traditional" way would be to add columns that have derived values and
include those columns in the query

table X
startnumber NUMBER
endnumber NUMBER
start_helper NUMBER (always has part of startnumnber)
end_helper NUMBER (always has part of endnumnber)

index on all four columns

SELECT *
FROM X
WHERE '3131' BETWEEN start_helper and end_helper
AND '31313131' BETWEEN startnumber AND endnumber;


More than this I cannot say, at this stage I would be experimenting to
check my understanding.