List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:September 15 2008 6:37pm
Subject:RE: make In statement non case sensitive?
View as plain text  
mysql> select "qq" in ("QQ");
+----------------+
| "qq" in ("QQ") |
+----------------+
|              1 |
+----------------+

mysql> select "donkey" = "DONKEY";
+---------------------+
| "donkey" = "DONKEY" |
+---------------------+
|                   1 |
+---------------------+

Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

>-----Original Message-----
>From: elzapp@stripped [mailto:elzapp@stripped] On Behalf Of Bard Aase
>Sent: Monday, September 15, 2008 1:53 PM
>To: Dan Nelson
>Cc: Paul Nowosielski; mysql@stripped
>Subject: Re: make In statement non case sensitive?
>
>On Mon, Sep 15, 2008 at 6:36 PM, Dan Nelson <dnelson@stripped>
>wrote:
>> In the last episode (Sep 15), Paul Nowosielski said:
>>> Hello,
>>>
>>> Is there a way to call an IN statement and have return results that
>>> are not case sensitive.
>>>
>>> SELECT name FROM name_table WHERE name IN ('joe','john','harry')
>>>
>>> would return the same as
>>>
>>> SELECT name FROM name_table WHERE name LIKE 'joe' OR name LIKE 'john'
>OR name LIKE 'harry'
>>
>> It already works this way.  All string comparisons in MySQL are
>> case-insensitive, unless you have specifically marked a column as
>> having a case-sensitive collation, or are using a binary type instead
>> of a text type:
>>
>> http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html
>>
>> mysql> create table name_table ( name varchar(20) );
>> Query OK, 0 rows affected (0.07 sec)
>>
>> mysql> insert into name_table values ('Joe'),('john'),('HARRY');
>> Query OK, 3 rows affected (0.09 sec)
>> Records: 3  Duplicates: 0  Warnings: 0
>>
>> mysql> SELECT name FROM name_table WHERE name IN
>('joe','john','harry');
>> +-------+
>> | name  |
>> +-------+
>> | Joe   |
>> | john  |
>> | HARRY |
>> +-------+
>> 3 rows in set (0.09 sec)
>>
>> mysql> SELECT name FROM name_table WHERE name LIKE 'joe' OR name LIKE
>'john' OR name LIKE 'harry' ;
>> +-------+
>> | name  |
>> +-------+
>> | Joe   |
>> | john  |
>> | HARRY |
>> +-------+
>> 3 rows in set (0.00 sec)
>>
>> mysql>
>
>This is true for LIKE statements, however, its not the case for IN or
>'donkey'="shubbery".
>Using LOWER() or UPPER() on the field you'd like to compare, and
>likewise on the list would work, but it might make a huge impact on
>the efficiency of the query.
>
>
>--
>MSN: msn@stripped
>http://blog.elzapp.com
>:wq
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.com




Thread
make In statement non case sensitive?Paul Nowosielski15 Sep
  • Re: make In statement non case sensitive?Jonathan Selander15 Sep
  • Re: make In statement non case sensitive?Dan Nelson15 Sep
    • Re: make In statement non case sensitive?Bard Aase15 Sep
      • RE: make In statement non case sensitive?Jerry Schwartz15 Sep
        • Re: make In statement non case sensitive?Bard Aase15 Sep