Hi,
I’m Brasilian.
I have a problem in H2 version 1.2.138.
When I run the query “select * from cliente where lower(nome) like lower(‘paula sartório%’)” in my database, the result is one tuple. It´s OK, but when I run the query “select * from cliente where lower(nome) like lower(‘paula sartorio%’)”, the result is empty.
In my database the table "cliente" on column "nome" the value is “Paula Sartório”.
FYI: Both queries run on MySQL and return the same result.
Att,
Rodrigo Macedo
In that case, SET COLLATION needs to be used:
http://h2database.com/html/grammar.html#set_collation
Regards,
Thomas
> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>
Did you read the documentation? You need to use 'strength'. PRIMARY is
usually case- and umlaut-insensitive; SECONDARY is case-insensitive
but umlaut-sensitive; TERTIARY is both case- and umlaut-sensitive;
IDENTICAL is sensitive to all differences and only affects ordering).
Example:
drop all objects;
set collation PORTUGUESE_BRAZIL STRENGTH PRIMARY;
create table test(name varchar);
insert into test values('a'), ('A'), ('à'), ('b');
select * from test where name = 'a';
select * from test order by name;
drop all objects;
set collation PORTUGUESE_BRAZIL STRENGTH SECONDARY;
create table test(name varchar);
insert into test values('a'), ('A'), ('à'), ('b');
select * from test where name = 'a';
select * from test order by name;
drop all objects;
set collation PORTUGUESE_BRAZIL STRENGTH IDENTICAL;
create table test(name varchar);
insert into test values('a'), ('A'), ('à'), ('b');
select * from test where name = 'a';
select * from test order by name;
Regards,
Thomas