Case insensitive search (MySQL)

Hello,

I'm looking for solution in case insensitive search in MySql.

Table exemple.

CREATE TABLE `pozycje` (
`id` int(11) NOT NULL auto_increment,
`eid` varchar(20) collate latin2_bin default NULL,
`poz_nazwa` varchar(40) collate latin2_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `pozycje_fk_user` FOREIGN KEY (`user_id`) REFERENCES
`users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin
COMMENT='InnoDB free: 0 kB; (`user_id`) ;

Data in table

id eid poz_nazwa
__________________________________________________
401 1 401-B12-1 Wrotki Band

402 1 402-B12-1 Wrotki Band

403 1 403-B12-3 Roman Giertych
Inc.
404 1 23-22-22 Greg Johan Inc.

405 1 25-21-45 Greg Johan Inc.

406 1 33-34-88 Greg Johan Inc.

407 1 45-09-22 Greg Johan Inc.

408 1 54-23-89 Greg Johan Inc.

409 1 09-65-43 Giertych INC

410 1 33-11-87 Greg Johan Inc.

Search Query:
SELECT * FROM pozycje Where poz_nazwa Like "%greg%"

Results: NONE!!!! WHY

Im looking for that:
Search Query:
SELECT * FROM pozycje Where poz_nazwa Like "%greg%"

Results: Found 6 rows

id eid poz_nazwa
__________________________________________________
404 1 23-22-22 Greg Johan Inc.

405 1 25-21-45 Greg Johan Inc.

406 1 33-34-88 Greg Johan Inc.

407 1 45-09-22 Greg Johan Inc.

408 1 54-23-89 Greg Johan Inc.
410 1 33-11-87 Greg Johan Inc.


Thanks for any response

Re: Case insensitive search (MySQL)

On Thu, 25 Jan 2007 01:24:07 -0800, iksando wrote:

> Hello,
>
> I'm looking for solution in case insensitive search in MySql.
>
> Table exemple.
>
> CREATE TABLE `pozycje` (
> `id` int(11) NOT NULL auto_increment,
> `eid` varchar(20) collate latin2_bin default NULL,
> `poz_nazwa` varchar(40) collate latin2_bin NOT NULL,
> PRIMARY KEY (`id`),
> KEY `user_id` (`user_id`),
> CONSTRAINT `pozycje_fk_user` FOREIGN KEY (`user_id`) REFERENCES
> `users` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin
> COMMENT='InnoDB free: 0 kB; (`user_id`) ;
>
> Data in table
>

>
> Search Query:
> SELECT * FROM pozycje Where poz_nazwa Like "%greg%"
>
> Results: NONE!!!! WHY

Because - the field [poz_nazwa] is specified "collate latin2_bin"
The "_bin" suggests Binary. Looking that up in the MySQL docs:

Collation Meaning
.....
latin1_bin Binary according to latin1 encoding

showing that it is indeed "Binary". Binary means that it will
compare case *sensitive* on that field. You will need to change
that collation in the field definition -
Or
you could force all uppercase with UCASE().
SELECT * FROM pozycje Where UCASE(poz_nazwa) Like UCASE("%greg%")
which effectively makes string comparisons case insensitive despite the
Binary character set chosen.

Thomas Bartkus