[Phpwm] MySQL case insensitive string comparison

[Nerijus Sumlinskas]

LIKE is case insensitive, and you could use LIKE BINARY if you want to 
have case sensitive as MySQL then compares strings as arrays of bytes, 
not characters. Try

select * from table where name LIKE 'MIKE';

or even 

select * from table where name LIKE '%MIKE%';

to find Mike even in the middle of sentance :)
 

Simon Emms wrote:
> LIKE BINARY is what MySQL suggests you use.
>
> "SELECT * FROM `table` WHERE name LIKE BINARY 'MIKE';"
>
> S
>
> -----Original Message-----
> From: phpwm-bounces at mailman.lug.org.uk
> [mailto:phpwm-bounces at mailman.lug.org.uk] On Behalf Of Mike Tipping
> Sent: 24 June 2010 13:39
> To: West Midlands PHP User Group
> Subject: [Phpwm] MySQL case insensitive string comparison
>
> Whenever I run a select in Mysql based on a string comparison, the
> comparison is always case sensitive so where 'name' in the DB is 'mike',
> select * from table where name = 'MIKE'; won't return results.
>
> Looking at the MySQL documentation it says that case sensitivity on
> comparisons depends on the collation and if I use 'latin1_swedish_ci' (which
> I do) the comparison will be case insensitive (which it isn't).
>
> It also says that STRCMP() is by default case insensitive, but when I try
> that it still doesn't return anything.
>
> Any ideas what I might be doing wrong or what I can use to do a case
> insensitive string comparison in MySQL.
>
> Cheers
>
> Mike
>
>
>
> _______________________________________________
> Phpwm mailing list
> Website : http://www.phpwm.org
> Twitter : http://www.twitter.com/phpwm
> Facebook: http://www.facebook.com/group.php?gid=2361609907
>
> Post to list: Phpwm at mailman.lug.org.uk
> Archive etc : https://mailman.lug.org.uk/mailman/listinfo/phpwm
>   
> ------------------------------------------------------------------------
>
> _______________________________________________
> Phpwm mailing list
> Website : http://www.phpwm.org
> Twitter : http://www.twitter.com/phpwm
> Facebook: http://www.facebook.com/group.php?gid=2361609907
>
> Post to list: Phpwm at mailman.lug.org.uk
> Archive etc : https://mailman.lug.org.uk/mailman/listinfo/phpwm
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 9.0.829 / Virus Database: 271.1.1/2959 - Release Date: 06/23/10 19:35:00
>
>