Khớp mẫu với SQL LIKE: Hướng dẫn thực hành kèm ví dụ

Giả sử bạn có bảng employees và muốn tìm tất cả tên bắt đầu bằng ‘A’:

Bạn có thể dành thời gian lướt bảng thủ công. Nhưng tại sao phải làm vậy khi đã có toán tử LIKE?

SELECT DISTINCT
	first_name
FROM employees
WHERE first_name LIKE 'A%'

Điều kỳ diệu nằm ở mệnh đề `WHERE first_name LIKE ‘A%’`, nghĩa là “tìm tất cả first_name bắt đầu bằng "A" và kết thúc bằng bất kỳ số lượng ký tự nào.” A% ở đây được gọi là một mẫu để khớp. 

% không phải là ký tự đại diện duy nhất bạn có thể dùng cùng với toán tử LIKE. Bạn cũng có thể dùng dấu gạch dưới _:

• `%` khớp với bất kỳ số lượng ký tự nào.
• _ khớp với đúng một ký tự.

Cú pháp rất dễ nhớ. Chỉ đơn giản là:

column_name LIKE pattern

Bạn có thể dùng LIKE để đạt được nhiều kiểu khớp mẫu. Cách làm như sau:

Ví dụ SQL LIKE dành cho người mới bắt đầu

Bên dưới, chúng tôi nêu một số ví dụ thực tế về cách bạn có thể dùng câu lệnh LIKE và các kết quả từ bộ dữ liệu mẫu của chúng tôi. 

1. Dùng LIKE để khớp chuỗi chính xác

Nếu bạn muốn thực hiện khớp chuỗi chính xác, hãy dùng LIKE mà không có ‘%’ hoặc ‘_’

SELECT
    first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’

2. Dùng ‘%’ để khớp bất kỳ số lượng ký tự nào 

‘%’ có thể dùng để khớp bất kỳ (kể cả không có) số lượng ký tự nào – số, chữ cái, hoặc ký hiệu.

Giả sử bạn muốn tìm tất cả nhân viên có tên bắt đầu bằng ‘Adam’; bạn có thể dùng mẫu ‘Adam%’

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Adam%'

Để tìm tên kết thúc bằng "z" hãy thử mẫu ‘%z’. Bạn cũng có thể dùng nhiều ‘%’ trong một mẫu. Ví dụ, nếu muốn tìm tên có chứa "z", hãy dùng ‘%z%’.

3. Dùng ‘_’ để khớp một (và chỉ một) ký tự

Giống trò chơi Hangman, dấu gạch dưới _ chỉ có thể khớp một ký tự.

Có bao nhiêu cách viết Le_n? Mẫu này sẽ khớp mọi thứ từ "Lexn", "LeAn", "Le3n", hoặc "Le-n".

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Le_n'

Có những tên nào chỉ gồm ba ký tự? Chúng ta có thể tìm bằng ba dấu gạch dưới liên tiếp ___ làm mẫu.

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '___'

4. Dùng cả ‘%’ và ‘_’ để khớp mọi mẫu

Tất nhiên, bạn có thể dùng cả ‘%’ và ‘_’ để tạo ra các mẫu thú vị. 

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '%ann_'

Mẫu ‘%ann’ khớp một chuỗi bắt đầu bằng bất kỳ số lượng ký tự nào và kết thúc bằng "ann" và một ký tự khác.

5. Dùng NOT để tìm chuỗi không khớp mẫu

Nếu bạn muốn tìm tất cả các hàng không khớp một tiêu chí cụ thể thì sao? Bạn có thể dùng toán tử NOT LIKE. Ví dụ, để tìm tất cả chức danh ngoại trừ "Staff", chúng ta có thể dùng cú pháp

`WHERE title NOT LIKE ‘Staff’`

Điều này hoàn toàn tương đương với cú pháp

WHERE title != 'Staff'

SELECT DISTINCT
    title
FROM titles
WHERE title NOT LIKE 'Staff'

Tất nhiên, bạn có thể dùng NOT LIKE với bất kỳ mẫu nào chúng tôi đã mô tả.

SELECT DISTINCT
    title
FROM titles 
WHERE title NOT LIKE '%engineer'

6. Dùng LOWER (hoặc UPPER) với LIKE để khớp mẫu không phân biệt hoa thường

Bạn có thể dùng cú pháp sau nếu cần khớp mẫu nhưng không chắc chuỗi được lưu dưới dạng chữ thường, chữ hoa, hay lẫn lộn.

LOWER(column_name) LIKE pattern

Hàm LOWER() trả về tất cả chuỗi ở chữ thường, bất kể chúng được lưu là chữ hoa, chữ thường, hay lẫn lộn. 

Khi dùng cú pháp này, hãy đảm bảo bạn viết mẫu hoàn toàn bằng chữ thường! Nếu không, bạn có thể không nhận được kết quả khớp.

Bạn cũng có thể thay LOWER bằng UPPER trong cú pháp trên. Hãy chắc chắn viết mẫu bằng chữ hoa.

`UPPER(column_name) LIKE PATTERN`

Ví dụ, để tìm xem tên nhân viên là "Joanne", "JoAnne", "Joanna", hay "JoAnna", hãy thử một trong các cách sau:

SELECT DISTINCT
    first_name
FROM employees
WHERE LOWER(first_name) LIKE 'joann_'

SELECT DISTINCT
    first_name
FROM employees
WHERE UPPER(first_name) LIKE 'JOANN_'

7. SQL LIKE với nhiều giá trị dùng OR/AND

Bạn cũng có thể kết hợp nhiều điều kiện bằng cú pháp LIKE. 

Ví dụ, dùng điều kiện OR để tìm kết quả thỏa mãn ít nhất một trong nhiều mẫu LIKE.

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE 'Ad_l' OR
first_name LIKE 'Ad_m'

Ngược lại, để tìm một chuỗi khớp nhiều điều kiện LIKE, hãy dùng từ khóa AND.

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE '%am%' AND
first_name LIKE '%me%'

Cú pháp LIKE có thể áp dụng cho nhiều cột miễn là kiểu dữ liệu của chúng là ký tự độ dài biến (varchar). Biết vậy, chúng ta có thể tìm tên nhân viên có chữ cái đầu là "Z. Z."

SELECT DISTINCT
    first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'

8. Dùng LIKE trong mệnh đề SELECT CASE WHEN 

Cho đến giờ, chúng ta tập trung sử dụng LIKE làm điều kiện chọn bản ghi trong mệnh đề WHERE. Chúng ta cũng dùng LIKE trong mệnh đề SELECT. Ví dụ, ta có thể tìm xem có bao nhiêu nhân viên tên "Adam" trong cơ sở dữ liệu không?

SELECT
    COUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam
FROM employees

Mặt khác, có bao nhiêu nhân viên có chữ cái đầu là "A.Z."?

SELECT
    COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employees

Các mẫu thường dùng trong SQL LIKE

Dưới đây là tóm tắt các mẫu chúng ta đã thảo luận để tham khảo nhanh:

Ví dụ SQL LIKE ở mức trung cấp

Hàm LIKE về cơ bản khá giống nhau giữa các hệ SQL khác nhau (ví dụ PostgreSQL, MySQL, Redshift, v.v.). Tuy nhiên, một số có các biến thể bổ sung của hàm LIKE đáng để nhắc đến. 

1. Toán tử ILIKE

Có sẵn trong Redshift và PostgreSQL, ILIKE là phiên bản không phân biệt hoa thường của LIKE. Do đó, tất cả các câu sau là tương đương.

SELECT
  datacamp ILIKE ‘datacamp’, -- returns TRUE
  DATACAMP ILIKE ‘datacamp’, -- returns TRUE
  Datacamp ILIKE ‘datacamp’, -- returns TRUE
  datacamp ILIKE ‘DataCamp’, -- returns TRUE

2. Dùng ngoặc vuông [] và [^] làm ký tự đại diện

Người dùng T-SQL hoặc SQL Server có thêm ký tự đại diện để khớp mẫu phức tạp hơn.

Cú pháp ngoặc vuông [] khớp bất kỳ ký tự cụ thể nào trong một khoảng hoặc tập. Ví dụ, các câu sau đều trả về TRUE.

SELECT
  ‘Carson’ LIKE ‘[C-K]arson’, -- returns TRUE because C is in the range C-K
  ‘Karson’ LIKE ‘[C-K]arson’, -- returns TRUE because K is in range
  ‘Larson’ LIKE ‘[CKL]arson’, -- returns TRUE because L is in the set [CKL]
  ‘Parson’ LIKE ‘[C-K]arson’ -- returns FALSE because P is out of range

Ký tự mũ trong ngoặc vuông [^] khớp bất kỳ một ký tự nào không nằm trong khoảng hay tập được chỉ định. Bạn có thấy vì sao các kết quả sau lại như vậy không? 

SELECT
  ‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE 
  ‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUE

Tại đây, vì "C" nằm trong khoảng [C-K], mẫu ‘C’ sẽ không khớp [^C-K]. Do đó, "Carson" sẽ không khớp [^C-K]arson, còn "Parson" thì sẽ.

3. Toán tử RLIKE

Có sẵn trong MySQL, toán tử RLIKE nhận biết các biểu thức chính quy (RegEx) trong mẫu. RegEx là công cụ mạnh mẽ và linh hoạt cho khớp mẫu nâng cao. 

Hiểu biết cơ bản về RegEx luôn hữu ích, đặc biệt nếu hệ SQL của bạn hỗ trợ RLIKE. Bạn có thể tìm hiểu thêm về RegEx với khóa học Biểu thức chính quy trong Python của chúng tôi.

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name RLIKE 'Susann[a-e]'

4. Toán tử ‘~~’

Trong PostgreSQL, ‘~~’ hoàn toàn đồng nghĩa với LIKE. Cũng có các tương đương của ILIKE, NOT LIKE, và NOT ILIKE.

Xử lý sự cố: Lỗi và sai sót thường gặp với LIKE

Dưới đây là một số lỗi thường gặp khi dùng LIKE, và cách khắc phục:

1. Không trả về kết quả: Kiểm tra phân biệt hoa thường. Trong một số biến thể SQL, LIKE mặc định phân biệt hoa thường. Dùng LOWER() hoặc ILIKE (nếu được hỗ trợ) để khớp không phân biệt hoa thường.
2. Kết quả bất ngờ với ký tự đại diện: Đảm bảo dùng đúng % và _. % khớp bất kỳ số lượng ký tự nào, còn _ khớp đúng một ký tự. Đặt sai có thể dẫn đến kết quả ngoài ý muốn.
3. Vấn đề hiệu năng: Nếu truy vấn chậm, hãy kiểm tra mẫu có % ở đầu (ví dụ, %pattern) vốn vô hiệu hóa chỉ mục. Viết lại mẫu để bắt đầu bằng chuỗi cụ thể nếu có thể. Xem phần bên dưới để biết thêm về hiệu năng. 
4. Lỗi SQL injection: Nếu bạn dùng đầu vào người dùng trong truy vấn, hãy đảm bảo tham số hóa đúng cách để tránh lỗ hổng SQL injection.
5. Ký tự đặc biệt trong mẫu: Các ký tự đại diện như % và _ trong chuỗi tìm kiếm phải được escape nếu dùng theo nghĩa đen. Dùng ESCAPE trong truy vấn để định nghĩa ký tự escape.

WHERE column_name LIKE '50\% OFF' ESCAPE '\'

Cân nhắc hiệu năng khi dùng toán tử LIKE

Toán tử LIKE rất hữu dụng, nhưng có thể ảnh hưởng đến hiệu năng truy vấn, đặc biệt khi dùng trên bộ dữ liệu lớn. Dưới đây là một số cân nhắc để tối ưu việc sử dụng:

1. Chỉ mục: Toán tử LIKE hoạt động tốt nhất khi mẫu bắt đầu bằng chuỗi hằng, như Adam%, vì cơ sở dữ liệu có thể dùng chỉ mục. Tuy nhiên, các mẫu như %Adam hoặc %Adam% yêu cầu quét toàn bảng, có thể chậm với bảng lớn.
2. Tránh ký tự đại diện ở đầu: Bắt đầu mẫu bằng %, như %pattern, sẽ vô hiệu hóa chỉ mục, vì cơ sở dữ liệu phải xem xét mọi bản ghi.
3. Đối chiếu và khớp không phân biệt hoa thường: Dùng các hàm như LOWER() hoặc UPPER() trên cột để tìm kiếm không phân biệt hoa thường cũng có thể khiến chỉ mục không được dùng. Thay vào đó, hãy đảm bảo collation của cơ sở dữ liệu được đặt phù hợp cho so sánh không phân biệt hoa thường.
4. Cách tiếp cận thay thế: Với bộ dữ liệu lớn, hãy cân nhắc dùng tìm kiếm toàn văn hoặc các tính năng tìm kiếm đặc thù của CSDL, như chỉ mục GIN indexes trong PostgreSQL hoặc chỉ mục FULLTEXT trong MySQL, khi thực hiện khớp chuỗi phức tạp hoặc thường xuyên.
5. Truy vấn chọn lọc: Giới hạn phạm vi truy vấn bằng các bộ lọc bổ sung, như khoảng ngày hoặc cột số, để giảm dữ liệu mà toán tử LIKE phải xử lý.

LIKE so với Tìm kiếm toàn văn

Tìm kiếm toàn văn có sẵn trong các CSDL như MySQL, PostgreSQL và SQL Server. Trong khi LIKE hoạt động tốt cho khớp mẫu đơn giản, tìm kiếm toàn văn được xây dựng cho các truy vấn văn bản nâng cao. So sánh hai cách tiếp cận như sau:

Đối với các thao tác chuỗi nâng cao hơn ngoài khớp mẫu, xem hướng dẫn SQL CONTAINS.

Tự tin sử dụng SQL LIKE

Thành thạo các hàm SQL là chìa khóa để thành công trong khoa học dữ liệu, và lệnh LIKE của SQL cũng không ngoại lệ. Sử dụng SQL tốt sẽ thúc đẩy mạnh mẽ tiến trình phân tích của bạn, vì vậy hãy chắc chắn học thật kỹ!

Để tìm hiểu thêm về SQL, hãy xem các nội dung sau:

• Khóa học Nhập môn SQL
• Khóa học SQL Trung cấp
• Giới thiệu mệnh đề WHERE trong SQL
• SQL Subquery: Hướng dẫn toàn diện
• Giới thiệu về SQL Joins
• Trung tâm học SQL
• Bảng cheat SQL cơ bản