Problem with Select Like

[GMail]

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

 

[michaelm]

Hi,

Note that this behaviour is documented in
http://www.h2database.com/html/features.html#compatibility

What is said is :
"Text comparison in MySQL is case insensitive by default, while in H2
it is case sensitive (as in most other databases). H2 does support
case insensitive text comparison, but it needs to be set separately,
using SET IGNORECASE TRUE. This affects comparison using =, LIKE,
REGEXP."

Regards,

Michaël

[Rodrigo Macedo]

Hi,

Thanks for your reply, but my problem is not "ignore case". I solved
this by using the lower function. The problem is the graphic accent
that is saved in the database, but when I do a search using the same
name without graphic accent, the result does not come.

Do you understand me?

Regards,
Rodrigo Macedo

On 2 jul, 04:33, michaelm <michael.mich...@free.fr> wrote:
> Hi,
>

> Note that this behaviour is documented inhttp://www.h2database.com/html/features.html#compatibility

[Thomas Mueller]

Hi,

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.
>
>

[Rodrigo Macedo]

Hi,

I've tried running the query with "collation" "ENGLISH" and
"PORTUGUESE_BRAZIL" does not work.

Do you have any tips for this to work?

Regards,
Rodrigo Macedo

On 2 jul, 09:39, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote:
> Hi,
>
> In that case, SET COLLATION needs to be used:http://h2database.com/html/grammar.html#set_collation
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

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

[Rodrigo Macedo]

Hi,

Yes I read the documentation, but I don't seen the java.text.Collator
documentation because I thought that package was H2 and as not found
there, I was test with "set collation PORTUGUESE_BRAZIL" only, so it
did not work.

Thank's a lot for your help and sorry for the inconvenience.
You are the best. I use this database since 2006 and never experienced
this situation uses.
Now it worked exactly as I expected.

Regards,
Rodrigo Macedo