[Phpwm] MySQL case insensitive string comparison

[Richard Cunningham]

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