[MySQL] Retrieve special character in MySQL.
-- Create a test table
1
2
3
4
5
6
7
8
9
10
11
| 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')
;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 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 for data start with "A%", it will not work as you've expected as below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 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 the same data as starting with "A".
So you need to use some escape sequence as below.
1
2
3
4
5
6
7
| SELECT * FROM CharTest where value like 'A$%B%' ESCAPE '$';
+-------+
| value |
+-------+
| A%BXX |
+-------+
1 row in set (0.00 sec)
|
1
2
3
4
5
6
7
| SELECT * FROM CharTest where value like 'A\\B%' ESCAPE '$';
+-------+
| value |
+-------+
| A\BXX |
+-------+
1 row in set (0.00 sec)
|
1
2
3
4
5
6
7
| SELECT * FROM CharTest where value like 'A''B%';
+-------+
| value |
+-------+
| A'BXX |
+-------+
1 row in set (0.00 sec)
|
1
2
3
4
5
6
7
| SELECT * FROM CharTest where value like 'A/B%';
+-------+
| value |
+-------+
| A/BXX |
+-------+
1 row in set (0.00 sec)
|
No comments:
Post a Comment