List:General Discussion« Previous MessageNext Message »
From:Bard Aase Date:September 15 2008 8:16pm
Subject:Re: make In statement non case sensitive?
View as plain text  
On Mon, Sep 15, 2008 at 8:37 PM, Jerry Schwartz
<jschwartz@stripped> wrote:
> mysql> select "qq" in ("QQ");
> +----------------+
> | "qq" in ("QQ") |
> +----------------+
> |              1 |
> +----------------+
>
> mysql> select "donkey" = "DONKEY";
> +---------------------+
> | "donkey" = "DONKEY" |
> +---------------------+
> |                   1 |
> +---------------------+

Yeah. I stand corrected. Not shure how I got this idea though.

>>-----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.
>>
>>
>>--
>>mvh base (Bård Aase)
>>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
>
>
>
>
>



-- 
mvh base (Bård Aase)
MSN: msn@stripped
http://blog.elzapp.com
:wq
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