[php] CaseSensitivity (Grrr..)

[Kristian =?iso-8859-1?Q?K=F6hntopp?=]

Armin Steiner wrote:
> nix ergebnis ... Kann ich MySQL irgendwie anweisen, Case-Insensitive zu
> werden ?
> Oder mittels PHP das irgendwie zusammenbasteln? (Upper/Lowercase hilft ja
> nix).

mysql> create table beispiel ( s varchar(80), t varchar(80) binary );
Query OK, 0 rows affected (0.29 sec)

mysql> insert into beispiel set s="forum", t="forum";
Query OK, 1 row affected (0.00 sec)

mysql> insert into beispiel set s="Forum", t="Forum";
Query OK, 1 row affected (0.00 sec)

mysql> insert into beispiel set s="FoRuM", t="FoRuM";
Query OK, 1 row affected (0.00 sec)

mysql> select s from beispiel where s="forum";
+-------+
| s     |
+-------+
| forum |
| Forum |
| FoRuM |
+-------+
3 rows in set (0.01 sec)

mysql> select t from beispiel where t="forum";
+-------+
| t     |
+-------+
| forum |
+-------+
1 row in set (0.00 sec)

mysql> select t from beispiel where lcase(t) = "forum";
+-------+
| t     |
+-------+
| forum |
| Forum |
| FoRuM |
+-------+
3 rows in set (0.00 sec)

mysql> select s from beispiel where binary s = "forum";
ERROR 1064: You have an error in your SQL syntax near 'binary s = "forum"' at line 1
mysql> select version();
+-------------+
| version()   |
+-------------+
| 3.22.21-log |
+-------------+
1 row in set (0.00 sec)


http://www.tcx.se/Manual_chapter/manual_Reference.html#Casts
7.4.7 Cast operators

BINARY 
The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be case sensitive even if the column isn't defined as BINARY or BLOB. 

     mysql> select "a" = "A";
             -> 1
     mysql> select BINARY "a" = "A";
             -> 0

     BINARY was introduced in MySQL 3.23.0 

+---+
 \H/
  v

-- 
Kristian Köhntopp, NetUSE Kommunikationstechnologie GmbH
Siemenswall, D-24107 Kiel, Germany, +49 431 386 436 00
Using PHP3? See our web development library at
http://phplib.netuse.de/ (We have moved! Update your bookmarks!)