List:General Discussion« Previous MessageNext Message »
From:Bard Aase Date:September 15 2008 5:52pm
Subject:Re: make In statement non case sensitive?
View as plain text  
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
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