[Phpwm] MySQL case insensitive string comparison

[Richard Cunningham]

What is the type of that column?

Mike Tipping wrote:
> 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
>
>