'cba', right. I don't mean to dismiss the sophisticated version as unnecessary; rather I was thinking there could be a "naive" flip() function where the raw codepoints were simply reversed irrespective of whether the source string contained combining forms, and a different, sophisticated reverse() function where combining forms are preserved during the flip.
The values in myFlippedColumn doesn't need to be sorted for any *display* purposes in my scenario. The flipped column does not need to be legible at all. It is simply acting as an ersatz so that ENDS-WITH searches ( LIKE '%value' ) can be done with an index.. The flip() function is applied to the search argument as well. So the naively flipper search-term is looked for in the naively flipped column. It doesn't matter for this purpose where a given value appears in the sort as long as the indexes are not confused by the sort order. Regards Igor Tandetnik wrote: > Tim Romano <tim.rom...@yahoo.com> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ------------------------------------------------------------------------ > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.425 / Virus Database: 270.14.69/2508 - Release Date: 11/17/09 > 07:40:00 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
