To make a string comparison case sensitive, cast (convert) one of the strings to binary form by using the BINA : String Compare « String « SQL / MySQL






  1. SQL / MySQL
  2. String
  3. String Compare

To make a string comparison case sensitive, cast (convert) one of the strings to binary form by using the BINA

      
RY keyword.
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  name    VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable (name)
    ->  VALUES
    ->          ('copper'),
    ->          ('gold'),
    ->          ('iron'),
    ->          ('lead'),
    ->          ('mercury'),
    ->          ('platinum'),
    ->          ('silver'),
    ->          ('tin')
    -> ;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM mytable;
+----------+
| name     |
+----------+
| copper   |
| gold     |
| iron     |
| lead     |
| mercury  |
| platinum |
| silver   |
| tin      |
+----------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT name, name = BINARY 'lead', BINARY name = 'LEAD' FROM mytable;
+----------+----------------------+----------------------+
| name     | name = BINARY 'lead' | BINARY name = 'LEAD' |
+----------+----------------------+----------------------+
| copper   |                    0 |                    0 |
| gold     |                    0 |                    0 |
| iron     |                    0 |                    0 |
| lead     |                    1 |                    0 |
| mercury  |                    0 |                    0 |
| platinum |                    0 |                    0 |
| silver   |                    0 |                    0 |
| tin      |                    0 |                    0 |
+----------+----------------------+----------------------+
8 rows in set (0.00 sec)

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








Related examples in the same category

1.Compare String in where clause
2.String comparisons are performed from left to right, one character at a time:
3.The two strings '4200' and '4200.0' are not the same:
4.Case-insensitive nature of string comparisons:
5.String comparisons in MySQL are not case sensitive by default:
6.To make a string comparison not case sensitive, convert both strings to the same lettercase using UPPER( ) or
7.NULL values fail comparisons both with < and with >=