Kursus
Manipulasi Data di SQL
4 Hr
323.6K
Suka atau tidak, operator LIKE itu penting dalam SQL. Operator ini memberi data engineer dan data scientist kemampuan untuk memfilter data berdasarkan kecocokan string tertentu. Dalam tutorial ini, saya akan memandu Anda menggunakan LIKE untuk pencocokan pola, dari dasar hingga teknik yang lebih lanjut.
Ringkasnya
- Operator SQL
LIKEmenggunakan dua wildcard untuk pencocokan pola:%(sejumlah karakter apa pun) dan_(tepat satu karakter) - Kombinasikan
LIKEdenganNOT,LOWER()/UPPER(),OR/AND, danCASE WHENuntuk pemfilteran yang fleksibel - Gunakan
ILIKE(PostgreSQL/Redshift) untuk pencocokan tidak peka huruf besar-kecil, atau bungkus kolom denganLOWER() - Wildcard
%di depan (misalnya,%pattern) menonaktifkan penggunaan indeks—pertimbangkan pencarian teks penuh untuk dataset besar - SQL Server mendukung
[]dan[^]untuk rentang karakter; MySQL mendukungRLIKEuntuk pencocokan berbasis regex
Operator SQL LIKE untuk Pencocokan Pola
Catatan: Untuk menjalankan semua kode contoh di tutorial ini sendiri, Anda dapat membuat workbook DataLab gratis dengan SQL yang telah terpasang dan basis data dengan data contoh.
Misalkan Anda memiliki tabel employees dan ingin mencari semua nama yang dimulai dengan ‘A’:
employees | |||||
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02T00:00:00.000Z | Georgi | Facello | M | 1986-06-26T00:00:00.000Z |
10002 | 1964-06-02T00:00:00.000Z | Bezalel | Simmel | F | 1985-11-21T00:00:00.000Z |
10003 | 1959-12-03T00:00:00.000Z | Parto | Bamford | M | 1986-08-28T00:00:00.000Z |
Anda bisa saja melihat tabel secara manual. Namun mengapa melakukannya jika ada operator LIKE?
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE 'A%'Keajaibannya ada pada klausa `WHERE first_name LIKE ‘A%’`, yang berarti “temukan semua first_name yang diawali huruf "A" dan diikuti sejumlah karakter apa pun.” A% di sini disebut sebagai pola untuk pencocokan.
% bukan satu-satunya wildcard yang dapat Anda gunakan bersama operator LIKE. Anda juga bisa menggunakan tanda garis bawah _:
`%`mencocokkan sejumlah karakter apa pun._mencocokkan tepat satu karakter.
Sintaksnya mudah diingat. Yaitu cukup:
column_name LIKE patternAnda dapat menggunakan LIKE untuk mencapai berbagai pencocokan pola. Begini caranya:
Contoh SQL LIKE untuk Pemula
Di bawah ini, kami merangkum beberapa contoh praktis bagaimana Anda dapat menggunakan pernyataan LIKE dan hasilnya dari kumpulan data contoh kami.
1. Gunakan LIKE untuk kecocokan string persis
Jika Anda ingin melakukan kecocokan string persis, gunakan LIKE tanpa ‘%’ atau ‘_’
SELECT
first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’2. Gunakan ‘%’ untuk mencocokkan sejumlah karakter apa pun
‘%’ dapat digunakan untuk mencocokkan sejumlah (bahkan nol) karakter—angka, huruf, atau simbol.
Misalkan Anda ingin menemukan semua karyawan dengan nama yang diawali ‘Adam’; Anda bisa menggunakan pola ‘Adam%’
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE 'Adam%'Untuk menemukan nama yang diakhiri dengan "z" gunakan pola ‘%z’. Anda juga bisa menggunakan beberapa ‘%’ dalam satu pola. Misalnya, jika Anda ingin menemukan nama yang mengandung "z", gunakan ‘%z%’.
3. Gunakan ‘_’ untuk mencocokkan satu (dan hanya satu) karakter
Seperti permainan Hangman, tanda garis bawah _ hanya dapat mengisi satu karakter.
Ada berapa cara mengeja Le_n? Pola tersebut akan mencocokkan apa pun dari "Lexn", "LeAn", "Le3n", atau "Le-n".
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE 'Le_n'Apa saja nama berbeda yang hanya memiliki tiga karakter? Kita dapat mengetahuinya menggunakan tiga garis bawah berurutan ___ sebagai polanya.
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE '___'4. Gunakan ‘%’ dan ‘_’ sekaligus untuk mencocokkan pola apa pun
Tentu, Anda dapat menggunakan ‘%’ dan ‘_’ sekaligus untuk membuat pola yang menarik.
SELECT DISTINCT
first_name
FROM employees
WHERE first_name LIKE '%ann_'Pola ‘%ann_’ mencocokkan string yang diawali dengan sejumlah karakter apa pun dan diakhiri dengan "ann" dan satu karakter lainnya.
5. Gunakan NOT untuk menemukan string yang tidak cocok dengan suatu pola
Bagaimana jika Anda ingin menemukan semua baris yang tidak memenuhi kriteria tertentu? Anda dapat menggunakan operator NOT LIKE. Misalnya, untuk menemukan semua title selain "Staff", kita dapat menggunakan sintaks
`WHERE title NOT LIKE ‘Staff’`Ini setara persis dengan sintaks
WHERE title != 'Staff'SELECT DISTINCT
title
FROM titles
WHERE title NOT LIKE 'Staff'Tentu, Anda dapat menggunakan NOT LIKE dengan pola apa pun yang telah kita jelaskan.
SELECT DISTINCT
title
FROM titles
WHERE title NOT LIKE '%engineer'6. Gunakan LOWER (atau UPPER) dengan LIKE untuk pencocokan pola tidak peka huruf besar-kecil
Anda dapat menggunakan sintaks berikut jika perlu melakukan pencocokan pola tetapi tidak yakin apakah string disimpan dalam huruf kecil, huruf besar, atau campuran.
LOWER(column_name) LIKE patternFungsi LOWER() mengembalikan semua string dalam huruf kecil, terlepas dari apakah awalnya disimpan dalam huruf besar, kecil, atau campuran.
Saat menggunakan sintaks tersebut, pastikan Anda menulis pola seluruhnya dengan huruf kecil! Jika tidak, Anda mungkin tidak mendapatkan kecocokan.
Anda juga bisa mengganti LOWER dengan UPPER pada sintaks di atas. Pastikan polanya ditulis dengan huruf kapital.
`UPPER(column_name) LIKE PATTERN`Misalnya, untuk mengetahui apakah nama seorang karyawan "Joanne", "JoAnne", "Joanna", atau "JoAnna", coba salah satu dari berikut ini:
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 dengan banyak nilai menggunakan OR/AND
Anda juga dapat menggabungkan beberapa kondisi menggunakan sintaks LIKE.
Misalnya, gunakan kondisi OR untuk menemukan hasil yang memenuhi setidaknya satu dari beberapa pola LIKE.
SELECT DISTINCT
first_name
FROM employees
WHERE
first_name LIKE 'Ad_l' OR
first_name LIKE 'Ad_m'Di sisi lain, untuk menemukan string yang memenuhi beberapa kondisi LIKE, gunakan kata kunci AND.
SELECT DISTINCT
first_name
FROM employees
WHERE
first_name LIKE '%am%' AND
first_name LIKE '%me%'Sintaks LIKE dapat diterapkan pada beberapa kolom selama tipe variabelnya adalah karakter panjang variabel (varchar). Dengan begitu, kita dapat menemukan nama karyawan yang inisialnya "Z. Z."
SELECT DISTINCT
first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'8. Gunakan LIKE dalam klausa SELECT CASE WHEN
Sejauh ini, kita fokus menggunakan LIKE sebagai kondisi untuk memilih record dalam klausa WHERE. Kita juga menggunakan LIKE di klausa SELECT. Misalnya, bisakah kita mengetahui berapa banyak karyawan bernama "Adam" di basis data kita?
SELECT
COUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam
FROM employeesDi sisi lain, berapa banyak karyawan yang memiliki inisial "A.Z."?
SELECT
COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employeesPola umum yang digunakan dalam SQL LIKE
Berikut ringkasan pola yang kita bahas untuk referensi cepat:
| Pola | Deskripsi | Contoh kasus penggunaan |
|---|---|---|
| A% | Mencocokkan string yang diawali dengan "A" | Menemukan nama yang diawali "A" |
| %z% | Mencocokkan string yang mengandung "z" | Menemukan nama yang mengandung "z" |
| Le_n | Mencocokkan string seperti "Len", "Leon", dll. | Menemukan nama dengan variasi satu karakter |
| %ann_ | Mencocokkan string yang diakhiri dengan "ann" dan satu karakter tambahan | Menemukan nama seperti "Joann", "Joanna" |
Contoh Menengah SQL LIKE
Fungsi LIKE sebagian besar serupa di berbagai varian SQL (mis. PostgreSQL, MySQL, Redshift, dll.). Namun beberapa memiliki variasi tambahan dari fungsi LIKE yang layak disebutkan.
1. Operator ILIKE
Tersedia di Redshift dan PostgreSQL, ILIKE adalah versi LIKE yang tidak peka huruf besar-kecil. Dengan demikian, semua hal berikut setara.
SELECT
datacamp ILIKE ‘datacamp’, -- returns TRUE
DATACAMP ILIKE ‘datacamp’, -- returns TRUE
Datacamp ILIKE ‘datacamp’, -- returns TRUE
datacamp ILIKE ‘DataCamp’, -- returns TRUE2. Menggunakan tanda kurung siku [] dan [^] sebagai wildcard
Pengguna T-SQL atau SQL Server memiliki karakter wildcard tambahan untuk pencocokan pola yang lebih kompleks.
Sintaks tanda kurung siku [] mencocokkan karakter spesifik mana pun dalam rentang atau himpunan. Misalnya, berikut ini semuanya akan mengembalikan 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 rangeWildcard tanda sisipan di dalam kurung siku [^] mencocokkan satu karakter apa pun yang tidak termasuk dalam rentang atau himpunan yang ditentukan. Bisakah Anda melihat mengapa hasil berikut demikian?
SELECT
‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE
‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUEDi sini, karena "C" berada dalam rentang [C-K], pola ‘C’ tidak akan cocok dengan [^C-K]. Jadi, "Carson" tidak akan cocok dengan [^C-K]arson, tetapi "Parson" akan cocok.
3. Operator RLIKE
Tersedia di MySQL, operator RLIKE mengenali regular expression (RegEx) dalam pola. RegEx adalah alat yang kuat dan serbaguna untuk pencocokan pola tingkat lanjut.
Tidak ada salahnya memahami dasar-dasar RegEx, terutama jika varian SQL Anda mendukung RLIKE. Anda dapat mempelajari lebih lanjut tentang RegEx melalui kursus Regular Expressions in Python kami.
SELECT DISTINCT
first_name
FROM employees
WHERE first_name RLIKE 'Susann[a-e]'4. Operator ‘~~’
Di PostgreSQL, ‘~~’ sepenuhnya sinonim dengan LIKE. Ada juga padanan untuk ILIKE, NOT LIKE, dan NOT ILIKE.
Operator | Padanan |
|
|
|
|
|
|
|
|
Pemecahan Masalah: Kesalahan dan Kekeliruan Umum dengan LIKE
Berikut beberapa kesalahan umum yang mungkin Anda temui saat menggunakan LIKE, dan cara memperbaikinya:
- Tidak ada hasil yang dikembalikan: Periksa kepekaan huruf besar-kecil. Dalam beberapa dialek SQL,
LIKEsecara bawaan peka huruf besar-kecil. GunakanLOWER()atauILIKE(jika didukung) untuk pencocokan yang tidak peka huruf besar-kecil. - Hasil tak terduga dengan wildcard: Pastikan
%dan_digunakan dengan benar.%mencocokkan sejumlah karakter apa pun, sedangkan_mencocokkan tepat satu karakter. Penempatan yang tidak tepat dapat menghasilkan kecocokan tak terduga. - Masalah kinerja: Jika kueri Anda lambat, periksa pola dengan
%di depan (misalnya,%pattern) yang menonaktifkan penggunaan indeks. Tulis ulang pola agar dimulai dengan string spesifik jika memungkinkan. Lihat bagian di bawah untuk detail kinerja. - Kesalahan injeksi SQL: Jika Anda menggunakan input pengguna dalam kueri, pastikan input diparameterisasi dengan benar untuk menghindari kerentanan injeksi SQL.
- Karakter khusus dalam pola: Simbol wildcard seperti
%dan_dalam string pencarian harus di-escape jika dimaksudkan sebagai karakter literal. GunakanESCAPEdalam kueri Anda untuk menentukan karakter escape.
WHERE column_name LIKE '50\% OFF' ESCAPE '\'Pertimbangan Kinerja Saat Menggunakan Operator LIKE
Operator LIKE sangat berguna, tetapi berpotensi memengaruhi kinerja kueri, terutama saat digunakan pada dataset besar. Berikut beberapa pertimbangan untuk mengoptimalkan penggunaannya:
- Indeks: Operator
LIKEbekerja paling baik saat pola dimulai dengan string konstan, sepertiAdam%, karena basis data dapat menggunakan indeks. Namun, pola seperti%Adamatau%Adam%memerlukan pemindaian seluruh tabel, yang bisa lambat untuk tabel besar. - Hindari wildcard di depan: Memulai pola dengan
%, seperti%pattern, menonaktifkan penggunaan indeks karena basis data harus memeriksa setiap record. - Kolasi dan pencocokan tidak peka huruf besar-kecil: Menggunakan fungsi seperti
LOWER()atauUPPER()pada kolom untuk pencarian tidak peka huruf besar-kecil juga dapat mencegah penggunaan indeks. Sebagai gantinya, pastikan kolasi basis data diatur sesuai untuk perbandingan tidak peka huruf besar-kecil. - Pendekatan alternatif: Untuk dataset besar, pertimbangkan menggunakan pencarian teks penuh atau fitur pencarian spesifik basis data, seperti indeks
GINindeks di PostgreSQL atau indeksFULLTEXTdi MySQL, saat melakukan pencocokan string yang kompleks atau sering. - Kueri selektif: Batasi cakupan kueri Anda menggunakan filter tambahan, seperti rentang tanggal atau kolom numerik, untuk mengurangi data yang diproses oleh operator
LIKE.
LIKE vs. Pencarian Teks Penuh
Pencarian teks penuh tersedia di basis data seperti MySQL, PostgreSQL, dan SQL Server. Sementara LIKE bekerja baik untuk pencocokan pola yang lugas, pencarian teks penuh dibangun untuk kueri teks yang lebih maju. Berikut perbandingan kedua pendekatan tersebut:
| Fitur | LIKE | Pencarian Teks Penuh |
|---|---|---|
| Sintaks | WHERE col LIKE '%term%' | MATCH(col) AGAINST('term') |
| Wildcard | %, _ | Operator boolean, bahasa alami |
| Dukungan indeks | Hanya pola prefiks (mis., term%) | Indeks teks penuh khusus (GIN, FULLTEXT) |
| Kinerja pada tabel besar | Lambat dengan % di depan | Dioptimalkan untuk kolom teks besar |
| Peringkat relevansi | Tidak | Ya |
| Fitur linguistik | Tidak | Stemming, stop words, sinonim |
| Terbaik untuk | Pola persis, dataset kecil hingga menengah | Pencarian kata kunci, kolom teks besar |
Untuk operasi string yang lebih lanjut di luar pencocokan pola, lihat tutorial SQL CONTAINS.
Gunakan SQL LIKE dengan Penuh Percaya Diri
Menguasai fungsi SQL adalah kunci untuk sukses di data science, dan perintah LIKE di SQL bukan pengecualian. Penguasaan SQL yang baik akan mempercepat kemajuan analitik Anda, jadi pastikan Anda mempelajarinya dengan saksama!
Untuk sumber daya lebih lanjut tentang SQL, pastikan Anda melihat yang berikut:
FAQs
Dapatkah operator LIKE digunakan dengan tipe data numerik?
Tidak, operator LIKE secara spesifik digunakan untuk pencocokan pola dengan tipe data string seperti CHAR, VARCHAR, dan TEXT. Untuk tipe data numerik, operator perbandingan lain seperti =, <, >, dll., yang digunakan.
Bagaimana kinerja LIKE dibandingkan operator SQL lainnya?
Operator LIKE dapat kurang efisien, terutama dengan pola yang dimulai dengan %, karena memerlukan pemindaian seluruh tabel. Pengindeksan tidak dapat digunakan secara efektif dalam kasus ini, yang dapat memperlambat kueri pada dataset besar.
Apakah ada masalah keamanan saat menggunakan LIKE di SQL?
Meskipun LIKE sendiri tidak secara inheren tidak aman, menggunakan input pengguna langsung dalam kueri SQL dapat menyebabkan serangan injeksi SQL. Selalu sanitasi input dan pertimbangkan menggunakan kueri berparameter untuk mengurangi risiko ini.
Bagaimana cara menangani pencarian peka huruf besar-kecil di varian SQL yang tidak mendukung ILIKE?
Dalam varian SQL tanpa ILIKE, Anda dapat menggunakan LOWER(column_name) LIKE LOWER(pattern) atau UPPER(column_name) LIKE UPPER(pattern) untuk melakukan pencarian tidak peka huruf besar-kecil.
Apakah LIKE dapat digunakan dengan karakter non-ASCII?
Ya, LIKE dapat digunakan dengan karakter non-ASCII selama enkoding basis data mendukung karakter tersebut. Ini termasuk UTF-8, yang umum digunakan untuk mendukung berbagai karakter.
Bagaimana Anda memodifikasi kueri LIKE untuk mencari karakter wildcard literal (mis., % atau _)?
Untuk mencari karakter literal % atau _, Anda perlu menggunakan karakter escape. Misalnya, di SQL Server, Anda bisa menggunakan LIKE 'A[%]%' ESCAPE '%' untuk mencari string yang mengandung karakter literal %.
Bisakah Anda menggabungkan LIKE dengan fungsi SQL lain untuk meningkatkan kemampuan pencarian?
Ya, menggabungkan LIKE dengan fungsi seperti CONCAT atau SUBSTRING dapat membantu memperhalus pola pencarian. Misalnya, menggunakan CONCAT dapat secara dinamis membangun pola berdasarkan nilai kolom lain.
Bagaimana LIKE dapat digunakan bersama operasi JOIN?
LIKE dapat diterapkan dalam kondisi JOIN untuk mencocokkan pola antar kolom dari tabel berbeda. Misalnya, ON table1.col1 LIKE table2.col2 || '%' dapat digunakan untuk menggabungkan tabel ketika table1.col1 dimulai dengan table2.col2.
Apa alternatif LIKE untuk pencocokan pola yang kompleks?
Untuk pola yang lebih kompleks, varian SQL yang mendukung regular expression, seperti RLIKE milik MySQL atau SIMILAR TO milik PostgreSQL, dapat digunakan. Ini menyediakan sintaks yang lebih kaya untuk pencocokan pola tingkat lanjut.
Bagaimana LIKE menangani nilai null dalam kolom?
Ketika sebuah kolom berisi nilai null, LIKE tidak akan mencocokkan record tersebut, karena null tidak dianggap sama dengan string atau pola apa pun. Untuk menyertakan null, gunakan kondisi seperti OR column IS NULL.
Topik
