LIKE so sánh văn bản với một mẫu, trong đó % khớp với một dãy ký tự bất kỳ và _ khớp đúng một ký tự, nên email LIKE '%@gmail.com' tìm ra các địa chỉ Gmail. LIKE phân biệt hoa thường. ILIKE so khớp tương tự nhưng bỏ qua hoa thường, còn ~ và ~* so khớp biểu thức chính quy (regex) POSIX.
Kết quả là Asha và Bella: ILIKE bỏ qua các chữ hoa trong địa chỉ của Bella, còn .co của Chen không khớp với phần đuôi.
So khớp với % và _
Toàn bộ chuỗi phải khớp với mẫu, nên một mẫu không có % hoạt động như =. NOT LIKE và NOT ILIKE đảo ngược kết quả.
A-___ khớp với A-100 nhưng không khớp với A-2000, vì mỗi _ đại diện cho đúng một ký tự. a-300 viết thường chỉ qua được phép kiểm tra ILIKE.
Tìm đúng ký tự % hoặc _
Dấu gạch chéo ngược escape ký tự đứng sau nó trong mẫu, nên '%50\%%' tìm văn bản chứa 50%. Khi dữ liệu có chứa dấu gạch chéo ngược, hãy chọn một ký tự escape khác bằng ESCAPE:
SAVE\_% chỉ khớp với SAVE_10. Không có escape, _ cũng sẽ khớp với chữ 1 trong SAVE10.
Biểu thức chính quy với ~
Các toán tử regex là ~ (khớp), ~* (khớp, bỏ qua hoa thường), !~ và !~* (không khớp). Một regex khớp ở bất kỳ vị trí nào trong chuỗi, trừ khi bạn neo nó bằng ^ và $:
Chỉ 555-0101 khớp với định dạng có gạch nối. regexp_replace với cờ g xóa mọi ký tự không phải chữ số, nên (555) 0199 thành 5550199 và call me thành chuỗi rỗng.
Trích xuất và đếm các kết quả khớp
Các hàm regex khác trả về phần đã khớp thay vì true hoặc false:
| Hàm | Trả về |
|---|---|
substring(s from 'pattern') | Kết quả khớp đầu tiên, hoặc nhóm trong ngoặc đầu tiên |
regexp_match(s, 'pattern') | Các nhóm của kết quả khớp đầu tiên dưới dạng text[] |
regexp_matches(s, 'pattern', 'g') | Một hàng cho mỗi kết quả khớp |
regexp_count(s, 'pattern') | Số lần khớp (từ PostgreSQL 15) |
regexp_substr(s, 'pattern') | Kết quả khớp đầu tiên (từ PostgreSQL 15) |
regexp_like(s, 'pattern') | Giống ~, ở dạng hàm (từ PostgreSQL 15) |
regexp_split_to_table(s, 'pattern') | Một hàng cho mỗi đoạn nằm giữa các kết quả khớp |
substring('Order 4521 shipped' from '\d+') trả về 4521. Chương so khớp mẫu trong tài liệu chính thức liệt kê các cờ và cú pháp regex.
Cho phép tìm kiếm theo mẫu dùng index
Một index B-tree có thể phục vụ mẫu được neo ở đầu, như LIKE 'abc%', khi cơ sở dữ liệu dùng collation C hoặc index được tạo với text_pattern_ops:
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
Một mẫu bắt đầu bằng %, ILIKE, và hầu hết các regex không dùng được B-tree. Với những trường hợp đó, extension pg_trgm thêm các index trigram phục vụ LIKE, ILIKE, ~ và ~*:
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
Với so sánh bằng không phân biệt hoa thường thay vì so khớp mẫu, một index trên lower(email), truy vấn bằng lower(email) = 'ben@work.io', là lựa chọn đơn giản hơn. Trang hàm chuỗi nói về lower, position và split_part.
Câu hỏi thường gặp
Làm sao kiểm tra một chuỗi có chứa chuỗi con trong PostgreSQL?
Dùng col LIKE '%text%', hoặc col ILIKE '%text%' để bỏ qua hoa thường. position('text' in col) > 0 và strpos(col, 'text') > 0 cũng được, và không cần escape % hay _.
LIKE trong PostgreSQL có phân biệt hoa thường không?
Có. 'Apple' LIKE 'a%' là false. Hãy dùng ILIKE, so sánh lower(col) LIKE 'a%', hoặc dùng toán tử regex không phân biệt hoa thường ~*.
Làm sao so khớp nhiều mẫu cùng lúc?
Kết hợp LIKE ANY với một mảng: name LIKE ANY (ARRAY['a%', 'b%']) đúng khi có bất kỳ mẫu nào khớp, còn LIKE ALL yêu cầu khớp tất cả. Một phép lựa chọn trong regex như name ~ '^(a|b)' cũng làm được điều tương tự.
SIMILAR TO trong PostgreSQL là gì?
Một sự pha trộn theo chuẩn SQL giữa LIKE và biểu thức chính quy: nó dùng % và _ cùng với |, *, + và các nhóm, và phải khớp toàn bộ chuỗi. LIKE hoặc các toán tử ~ thường dễ hiểu hơn.