Search the Internet for an SQLite extension called "unifuzz.c" and see if that does what you want in the way of character folding. I have a copy of the code on my other computer if you cannot find the original authors original code.
I didn't write it, but it basically implements a NOCASE collation which also also folds accents and other non-english characters to ASCII somewhat like the standard latin1_CI_AI in other SQL implementations. It also has some other related collations and folding functions. You can compile as an extension or with very minor modifications incorporate it directly into the amalgamation and compile your own engine (and shell) with the extension and collations pre-loaded. On Wed, 30 Oct 2013 13:51:26 +0100 Ulrich Goebel <m...@fam-goebel.de> wrote: >Hallo, > >for a SQLite db I would like to define a collation for german >"Umlaute" (don't know the english word for that, sorry) and >"Sonderzeichen" (äöü, ÄÖÜ, ß), so that: > >a=A=ä=Ä >o=O=ö=Ö >u=U=ü=Ü >ß=s (or, better: ß=ss) > >I want to use such a collation even on columns which are indeces, so I >would like to "connect" the collation to the column/index at the time >creating the column/index, not only in the later SELECTs. And I >suppose, that I really don't have to specify the collation in the >SELECTs if it is connected to the index. Is that right? > >It should work like that: > >CREATE TABLE person (name text); >CREATE INDEX idx_name on person (name COLLATE umlaute); > >and later on > >SELECT name FROM person ORDER BY name; > /* whithout specifying the collation again! */ > >should give > > Ortin > Ötzkök > Pandulas > Zurmühlen > >instead of > > Ortin > Pandulas > Zurmühlen > Ötzkök > >I'm sure, that I'm not the first fellow, who would like, but I don't >find a solution for my problem. > >Any help will be very wellcome! > >By the way: I define my db, tables and indices using a SQL script, >which I execute by the .read command in sqlite3 (under Ubuntu Linux). >But I don't find any possibility to define any collation. I just found >how to use an allredy existing collation. Isn't it possible, what I >want? > >Another way could be possible: I use python as my programming >language, with the apsw module to connect to the SQLite db. There is a >method apsw.connection.createcollation, which registers a >(python)-sorting-(collate-)function as a collation. That can be used >later, for example in SELECTs. Could it also be used in CREATE TABLEs >or CREATE INDEXs? In this case I could define my db, tables and >indices within python/apsw instead of the SQL-script and .read. Right? > >The background is very simple: in my db I store german people with >their name, address and so on, and there are these "Umlaute" in the >names, and the row name has its own index. > >Now I brought more text then I would like to... but I hope for Your >help! > >Ulrich > > >-- >Ulrich Goebel >Paracelsusstr. 120, 53177 Bonn >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ---- #include <shrinkwrap-disclaimer.h> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
