_：匹配任意單字符%：匹配任意數量字符（含 0 個）LIKE / NOT LIKE（不可用 =/!=）| 需求 | SQL |
|---|---|
| 以 "b" 開頭 | SELECT * FROM student WHERE name LIKE "b%"; |
| 以 "b" 結尾 | SELECT * FROM student WHERE name LIKE "%b"; |
| 包含 "b" | SELECT * FROM student WHERE name LIKE "%b%"; |
| 正好 5 字符 | SELECT * FROM student WHERE name LIKE "_____"; |
REGEXP / NOT REGEXP[aA] 同時匹配大小寫| 符號 | 含義 |
|---|---|
. | 任意單字符 |
* | 前字符 0 或多次 |
^ | 字符串開頭 |
$ | 字符串結尾 |
[abc] | 字符類，匹配 a/b/c 任一 |
[^abc] | 否定字符類 |
| 需求 | SQL |
|---|---|
| 以 "b" 開頭（不分大小寫） | SELECT * FROM student WHERE name REGEXP "^[bB]"; |
| 以 "b" 結尾 | SELECT * FROM student WHERE name REGEXP "[bB]$"; |
| 包含 "b" | SELECT * FROM student WHERE name REGEXP "[bB]"; |
| 正好 5 字符 | SELECT * FROM student WHERE name REGEXP "^.....$"; |
LIKE，可走索引（LIKE 'abc%'）REGEXPREGEXP 不走索引，謹慎使用
