Accent Insensitivity

[Horst]

Hi H2 users,


how can one search for words with accented characters like in 'École'
ignoring the accents (diacritics).
I would like to find this row when searching for 'ecole' without
accents.

In newer Oracle DB and some other DBs you can do something like this:
alter session set nls_sort='BINARY_AI' ;
Here '_AI' means "Accent Insensitivity"

Is there any way to do this in H2?

Regards,
Horst

[Thomas Mueller]

Hi,

Yes, see http://www.h2database.com/html/grammar.html#setcollation and the javadocs for java.text.Collator.

DROP ALL OBJECTS;
SET COLLATION GERMAN STRENGTH SECONDARY;
CREATE TABLE TEST(NAME VARCHAR);
INSERT INTO TEST VALUES('Güdel'), ('Gustav'), ('Guacamole');
SELECT * FROM TEST ORDER BY NAME;

Regards,
Thomas

[Horst]

Hi Thomas,

thanks for your answer, now it works nearly the way I would like to.
What I have is a DB with international addresses used only by germans.
So the problem for the users is that they don't know about all those
accents é or è or í and so on.
But they know the difference between ä and a.
If I set the collator's strength to SECONDARY the DB won't find é if I
search for e.
With PRIMARY strength it does but more than that now ü equals to u.
I think I have to have a look at H2s implemantation of the Collator
base class to better understand
the rules of it.
Where can I find the source code for that?

Regards,
Horst


On Feb 13, 10:53 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> Yes, seehttp://www.h2database.com/html/grammar.html#setcollationand the

> javadocs for java.text.Collator.
>
> DROP ALL OBJECTS;
> SET COLLATION GERMAN STRENGTH SECONDARY;
> CREATE TABLE TEST(NAME VARCHAR);
> INSERT INTO TEST VALUES('Güdel'), ('Gustav'), ('Guacamole');
> SELECT * FROM TEST ORDER BY NAME;
>
> Regards,
> Thomas
>

> On Feb 13, 2008 12:17 PM, Horst <performa...@phenomenow.de> wrote:
>
>
>
>
>
> > Hi H2 users,
>
> > how can one search for words with accented characters like in 'École'
> > ignoring the accents (diacritics).
> > I would like to find this row when searching for 'ecole' without
> > accents.
>
> > In newer Oracle DB and some other DBs you can do something like this:
> > alter session set nls_sort='BINARY_AI' ;
> > Here '_AI' means "Accent Insensitivity"
>
> > Is there any way to do this in H2?
>
> > Regards,

> > Horst- Hide quoted text -
>
> - Show quoted text -

[Thomas Mueller]

Hi,

The standard Java Collator is used. For details, see the Javadocs of java.text.Collator.

Regards,
Thomas