Tim Romano <[email protected]> wrote: > Understood that an index cannot be placed on a function; I wasn't > thinking of a "virtual field" as one can have in Oracle or MS-Access, > or in legacy non-1NF databases such as Revelation, for example. The > flip() function would simply be a utility that would enable me to do > this: > > update T set myFlippedColumn = flip(myNormalColumn) > > so that if myNormalColumn contained 'abc' myFlippedColumn contains > 'bca'.
You mean 'cba', right? > Regarding your point about combining Unicode forms: no need for the > function to so anything special in the way it reverses strings > containing combining forms. When you say "no need", do you mean that _you_ don't need that, or that you believe no one would ever need that? If the latter, what is this belief based on? > That is, no need whatsoever to treat > combining forms as monads, preserving them during the flip, so that 'a > b fi c d' would become 'd c fi b a' rather than 'd c if b a'. Let > the flipped string become 'd c if b a' ! Keep the function purely > determinative as if combining-forms never existed. The flipped string > doesn't have to be *legible*. But for your goals, it has to be sortable, right? In a proper Unicode collation, U+0041 U+0301 would behave quite differently from U+0301 U+0041. Consider "A ' E" (where ' stands for a combining acute accent). In most locales, this would sort between AE and BE. Now, if we reverse it naively, we'll end up with "E ' A", with the accent now attached to E and not A. The result would sort between EA and FA, rather than between EA and EB as you would probably want. > So, for example, if one wanted to find all rows where myNormalColumn > ENDS WITH 'fi c d', one could search myFlippedColumn like this: > > select * from LEXICON where myFlippedColumn LIKE 'd c if%' -- > allows index use > > This doesn't really require combining-form intelligence Your example doesn't show combining diacritics, it shows ligatures. I think it might not be as pretty with combining marks. Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
