DAVID -- Say you are wanting to search case-insensitively on a column
named ENAME for a string entered as 'June'. Then, just to be
clear, that means you'd want to locate:
June
JUne
JUNe
JUNE
june
jUne
:
junE
If you want to still use the index on ENAME, you could phrase
your query such that -- in addition to the UPPER(ENAME) =
UPPER('June') clause -- you also try the four permutations of
the first two letters in the word. Like:
WHERE ((ENAME LIKE 'JU%' OR
ENAME LIKE 'Ju%' OR
ENAME LIKE 'jU%' OR
ENAME LIKE 'ju%')
AND UPPER(ENAME) = UPPER('June'))
You obviously could take permutations on the first three
letters, or more, but two letters typically makes the index
selective enough to be much more useful than a full-table
scan.
The generic code to prepare the "four-way" or clause above
for any string 2 letters or longer would be fairly
straightforward to put together. I just wanted to suggest the
technique to you.
Hope this is of assitance.
--
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Steve Muench Email: smuench_at_oracle.com
Forms Development
Product Manager
