[Phpwm] MySQL case insensitive string comparison

[Mike Tipping]

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