[Phpwm] MySQL case insensitive string comparison

[Simon Emms]

What version of MySQL are you using and on what OS?  Perhaps that's the
reason.

If you want to send me over the string and export the data, I'll run it on
my DB server and see if there's an error in the table.

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 16:57
To: West Midlands PHP User Group
Subject: Re: [Phpwm] MySQL case insensitive string comparison

Nope in my case LIKE is case sensitive.

And my default collation is utf8_ci so it should follow that the collation
is the same on the server and the table.

It's very odd.


On 24/06/2010 14:31, "Richard Cunningham" <richard at richardcunningham.co.uk>
wrote:

> LIKE is case insensitive in MySQL
mysql> SELECT 'mike' LIKE
> 'MIKE';
+--------------------+
| 'mike' LIKE 'MIKE' |
+--------------------+
|
> 1 |
+--------------------+
1 row in set (0.00 sec)

(though be sure to escape
> %s and _s if it's user entered data for that
though)


Doing the LOWER(name)
> will stop MySQL using the index properly, since it
will have to do full
> table/index scan to convert every name to lower
case before doing the
> comparison.

Mike Tipping wrote:
> There isn't a iLike in Mysql. :o(
>
> I¹ve
> stated to use
>
> select * from table where lower(name) = lower(Œ$value¹)
>
>
> Which works but that doesn¹t seem a very efficient way of tying to get 
> it to behave as it should be behaving in the first place.
>
>
>
>
> On 24/06/2010
> 13:49, "Dan Dart" <dandart at googlemail.com> wrote:
>
>     Have you tried using
> the lower/upper/camel case instructions in
>     PHP to make MySQL see what
> you want?
>
>     Jolly confusing how it all seems to contradict itself.
>
>
> On 24 Jun 2010 13:39, "Mike Tipping" <mike at etuna.co.uk> wrote:
>
>
> 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
>
>
> ----------------------------------------------------------------------
> --
>
>
> _______________________________________________
> 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



_______________________________________________
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
-------------- next part --------------

No virus found in this outgoing 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