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