[Phpwm] MySQL case insensitive string comparison

Nerijus Sumlinskas nerijus at discountdomainsuk.com
Thu Jun 24 13:56:34 UTC 2010


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




More information about the Phpwm mailing list