Oracle comparison operators versus ORDER BY sequence
Posted on 2009-12-16
I have a simple problem. I need to navigate a long list of names, using mixed upper and lower case. The ORDER BY clause will sort them in alphabetical order (case insensitive), but comparison operators are case sensitive. For example, if I need the next name after "Hello", I could write:
SELECT * FROM TheTable WHERE TheName > 'Hello' ORDER BY TheName
) WHERE ROWNUM = 1;
The next name could be "help", but not "HELP", so it's inconsistent with the ORDER BY clause.
I know I can solve this problem using functions, but I need this to be an index search (it must be fast). So my question is two-fold:
1) Is there an operator like > meaning "later in standard alphabetical order" instead of "using greater character codes"?
2) Does Oracle perform an index search for
NLS_UPPER(TheName) > NLS_UPPER('Hello')
This would solve the problem, naturally. It seems unnatural that this expression could be optimised, but I'm not familiar with Oracle.
Markus -- (°v°)