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

-- 
	Dan Nelson
	dnelson@stripped
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