ค้นหาคำเฉพาะใน MySQL ด้วย Regular Expression (REGEXP)

ใช้ REGEXP ค้นหาตามรูปแบบ Regular Expression

mysql> CREATE TABLE DemoTable (
    -> Title VARCHAR(255)
    -> );
Query OK, 0 rows affected (1.82 sec)

mysql> INSERT INTO DemoTable VALUES('John is a good player');
Query OK, 1 row affected (0.26 sec)
mysql> INSERT INTO DemoTable VALUES('No,John Doe is not a good player');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DemoTable VALUES('Yes,Adam Smith is play cricket match');
Query OK, 1 row affected (0.28 sec)
mysql> ALTER TABLE DemoTable ADD FULLTEXT(Title);
Query OK, 0 rows affected, 1 warning (10.60 sec)

mysql> SELECT * FROM DemoTable;

+--------------------------------------+
| Title                                |
+--------------------------------------+
| John is a good player                |
| No,John Doe is not a good player     |
| Yes,Adam Smith is play cricket match |
+--------------------------------------+
3 rows in set (0.00 sec)

ค้นหาที่มีคำ John อยู่ในข้อความ:

mysql> SELECT * FROM DemoTable WHERE Title REGEXP '^.*John.*$';

+----------------------------------+
| Title                            |
+----------------------------------+
| John is a good player            |
| No,John Doe is not a good player |
+----------------------------------+
2 rows in set (0.11 sec)

หมายเหตุ: REGEXP ไม่ใช้ดัชนี FULLTEXT (คำสั่ง ALTER ADD FULLTEXT ในตัวอย่างไม่เกี่ยวข้องกับ REGEXP) สำหรับค้นหาข้อความเต็มควรใช้ MATCH AGAINST