Convert the output column to binary and sort that : Binary « String « SQL / MySQL






  1. SQL / MySQL
  2. String
  3. Binary

Convert the output column to binary and sort that

       
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> SELECT BINARY tstr FROM textblob_val ORDER BY 1;
+-------------+
| BINARY 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.To make tstr case sensitive, use BINARY:
6.Get the binary representation of the values 6 and 10.
7.Get the decimal values that belong to the binary representations 1001 and 111.
8.Binary Character String Comparison
9.Controlling Case Sensitivity of String Sorts
10.Mix binary and nonbinary string columns within a single table.