Creating a customized collator

For example, suppose you want to define a collation order to make Greek characters sort near their Latin equivalents ('α' near 'a', ''β' near 'b', and so on). You could define another locale with a CollatorProvider that returns a java.text.RuleBasedCollator with ever rules you want. See the API documentation for the RuleBasedCollator class for details about how you specify rules. In its simplest form, a set of rules might look like "a,A < b,B < c,C", which means more or less that 'a' and 'A' should be sorted before 'b' and 'B', which should be sorted before 'c' and 'C'. So to get the Greek characters sorted near similar Latin characters, define a CollatorProvider that looks like this one:

public class MyCollatorProvider extends CollatorProvider {
    public Locale[] getAvailableLocales() {
        return new Locale[] {
            new Locale("en", "US", "greek")
        };
    }
    
    public Collator getInstance(Locale locale) {
        StringBuilder rules = new StringBuilder();
        // alpha should go between a and b
        rules.append("< a,A < \u03b1,\u0391 < b,B");
        // beta should go between b and c
        rules.append("& b,B < \u03b2,\u0392 < c,C");
        // add more rules here ....

        try {
            return new RuleBasedCollator(rules.toString());
        } catch (ParseException pe) {
            throw new Error(pe);
        }
    }
}

Again, put the compiled class and the META-INF/services/java.text.spi.CollatorProvider file in a jar file, and start the ij tool with the -Djava.ext.dirs=. option in the directory where the jar file is located. Create a database that uses the new locale and insert some data with both Greek and Latin characters:

ij> connect 'jdbc:derby:GreekDB;territory=en_US_greek;collation=TERRITORY_BASED;create=true';
ij> create table t (x varchar(12));
0 rows inserted/updated/deleted
ij> insert into t values 'a', 'b', 'c', 'α', 'β';
5 rows inserted/updated/deleted
ij> select * from t order by x;
X           
------------
a
α
b
β
c 

5 rows selected

The ordering is just as you wanted it, with the Greek characters between the Latin ones, and not at the end where they would normally be located.

One word of caution: If, after you have created a database, you update your custom CollatorProvider so that the ordering is changed, you will need to recreate the database. You must do this because the indexes in the database are ordered, and you may see strange results if the indexes are ordered with a different collator from the one your database is currently using.