This is interesting, but my first thought is then I'd have a second table with 40 million records (the length of our field is 80), in addition to the original table with 500K records. Seems excessive to be able to search a single field. :)

-----------

OK, if it is for a single table and field, and you need to start at any position, I think you can do it yourself. A generic solution might require some additional work.

I would use this approach:

Let's assume your field has 30 characters.

Create a separate table, with 30 records for each record in your original table.

Fields: a link to the PK of the main table, a number (1-30), to identify the position, and a 30-character expression.

Copy the following expression to each of the 30 records:


upper(padr(substr(MyValue, i), 30))
Where MyValue is the field you want to index on, and i ranges from 1-30.

Update the secondary table automatically, with a trigger.

HTH,

Hilmar.