Subject Matching regarding primary differences
Author peter_jacobi.rm
For implementing incremental search in a GUI,
I'm looking how to write a SELECT, which will
find all rows where a columns starts with a
specific string (as in LIKE('abc%')), but taking
into account only primary character differences
(disregarding case and accents).

I'm aware of using David Schnepper's work on
nocase and nocase/noaccent collations, but after
thinking again about the problem, it seems better
to try to solve the problem without them (as I want
the output sorted according all differences).

What's missing so far is a method how to 'increment'
the search string to next string of equal length sorting
higher according to primary differences.

This 'incrementing' would return 'abd' for 'abc', and 'ac_' or
'abä' (depending on locale, err... collation) for 'abz'.

Having this incremented string the query would like
(:search holding the looked for string, :next holding
the incremented string):

> select col from t where
> (col >= upper(:search) or col >= lower(:search)) and
> col < upper(:next) and col < lower(:next);

The upper/lower thing must be done as it varies from collation
to collation which casing sorts lower. I hope it don't vary
from collation to collation that the unaccented letter sort

Any ideas for implementing the 'increment'?

Any alternatives for this requirements?

Best Regards,
Peter Jacobi,
Hamburg, Germany