On Wed, Oct 30, 2013 at 8:51 AM, 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);
>
If, instead, you say:
CREATE TABLE person(name text COLLATE umlaute);
CRETE INDEX idx_name ON person(name);
Then "umlaute" becomes the default collating sequence for the
person.namecolumn. It is used everywhere, unless overridden. So it
is used on the
index. And it will get used in your ORDER BY clause.
If you specify the collating sequence on the index only, then it is used
only on the index. Then you'd have to specify it again on the order by
clause ("... ORDER BY name COLLATE umlaute") in order to get it take effect
there.
>
> 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<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
