Retrieve special character in MySQL
https://dev.mysql.com/doc/refman/5.6/en/string-literals.html
Retrieve special character in MySQL.
ex.
DROP TEMPORARY TABLE IF EXISTS CharTest;
CREATE TEMPORARY TABLE CharTest
(value varchar(100));
INSERT INTO CharTest VALUES
('ABXX'), ('ACYY'),
('A%BXX'), ('A%CYY'),
('A\\BXX'), ('A\\CYY'),
('A''BXX'), ('A''CYY'),
('A/BXX'), ('A/CYY')
;
SELECT * FROM CharTest;
+-------+
| value |
+-------+
| ABXX |
| ACYY |
| A%BXX |
| A%CYY |
| A\BXX |
| A\CYY |
| A'BXX |
| A'CYY |
| A/BXX |
| A/CYY |
+-------+
10 rows in set (0.00 sec)
If you want to search data start with "A%".
It will not work properly as below.
SELECT * FROM CharTest where value like 'A%';
+-------+
| value |
+-------+
| ABXX |
| ACYY |
| A%BXX |
| A%CYY |
| A\BXX |
| A\CYY |
| A'BXX |
| A'CYY |
| A/BXX |
| A/CYY |
+-------+
10 rows in set (0.00 sec)
The result is exactly same data as start with "A".
So you need to use some escape sequence as below.
SELECT * FROM CharTest where value like 'A$%B%' ESCAPE '$';
+-------+
| value |
+-------+
| A%BXX |
+-------+
1 row in set (0.00 sec)
SELECT * FROM CharTest where value like 'A\\B%' ESCAPE '$';
+-------+
| value |
+-------+
| A\BXX |
+-------+
1 row in set (0.00 sec)
SELECT * FROM CharTest where value like 'A''B%';
+-------+
| value |
+-------+
| A'BXX |
+-------+
1 row in set (0.00 sec)
SELECT * FROM CharTest where value like 'A/B%';
+-------+
| value |
+-------+
| A/BXX |
+-------+
1 row in set (0.00 sec)
As you can see, "/" is not a special character.