Controlling Case Sensitivity of String Sorts : Binary « String « SQL / MySQL






  1. SQL / MySQL
  2. String
  3. Binary

Controlling Case Sensitivity of String Sorts

      
mysql>
mysql>
mysql> CREATE TABLE textblob_val
    -> (
    ->  tstr    TEXT,   # not case sensitive
    ->  bstr    BLOB    # case sensitive
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO textblob_val (tstr,bstr) VALUES('aaa','aaa'),
    ->                                            ('AAA','AAA'),
    ->                                            ('bbb','bbb'),
    ->                                            ('BBB','BBB');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM textblob_val;
+------+------+
| tstr | bstr |
+------+------+
| aaa  | aaa  |
| AAA  | AAA  |
| bbb  | bbb  |
| BBB  | BBB  |
+------+------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT tstr FROM textblob_val ORDER BY BINARY tstr;
+------+
| tstr |
+------+
| AAA  |
| BBB  |
| aaa  |
| bbb  |
+------+
4 rows in set (0.00 sec)

mysql>
mysql> drop table textblob_val;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Using Binary with REGEXP
2.Force name values to be case sensitive using BINARY
3.Using BINARY has the effect of causing [:lower:] and [:upper:] in regular expressions to act as you would expe
4.A pattern match against a binary column is case sensitive.
5.Convert the output column to binary and sort that
6.To make tstr case sensitive, use BINARY:
7.Get the binary representation of the values 6 and 10.
8.Get the decimal values that belong to the binary representations 1001 and 111.
9.Binary Character String Comparison
10.Mix binary and nonbinary string columns within a single table.