Problem with Select Like

Showing 1-7 of 7 messages
Problem with Select Like Rodrigo Macedo 7/1/11 4:42 PM

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

 

Re: Problem with Select Like michaelm 7/2/11 12:33 AM
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
Re: Problem with Select Like Rodrigo Macedo 7/2/11 4:49 AM
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
Re: Problem with Select Like Thomas Mueller 7/2/11 5:39 AM
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.
>
>

Re: Problem with Select Like Rodrigo Macedo 7/2/11 7:09 AM
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
>
Re: Problem with Select Like Thomas Mueller 7/2/11 8:02 AM
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

Re: Problem with Select Like Rodrigo Macedo 7/2/11 10:04 AM

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