Menu

LIKE, ILIKE, dan Regular Expression di PostgreSQL

LIKE mencocokkan teks dengan pola memakai % dan _, ILIKE melakukan hal yang sama tanpa memedulikan huruf besar kecil, dan operator ~ mencocokkan regular expression POSIX.

Halaman ini dilengkapi editor yang bisa dijalankan. Edit, jalankan, dan lihat output-nya seketika.

LIKE membandingkan teks dengan pola, di mana % cocok dengan deretan karakter apa pun dan _ cocok dengan tepat satu karakter, jadi email LIKE '%@gmail.com' menemukan alamat Gmail. LIKE bersifat case-sensitive. ILIKE adalah pencocokan yang sama tanpa memedulikan huruf besar kecil, sedangkan ~ dan ~* mencocokkan regular expression POSIX.

Hasilnya Asha dan Bella: ILIKE mengabaikan huruf kapital di alamat Bella, dan .co milik Chen tidak cocok dengan akhirannya.

Mencocokkan dengan % dan _

Seluruh string harus cocok dengan pola, jadi pola tanpa % berperilaku seperti =. NOT LIKE dan NOT ILIKE membalik hasilnya.

A-___ cocok dengan A-100 tapi tidak dengan A-2000, karena setiap _ mewakili tepat satu karakter. a-300 yang huruf kecil hanya lolos uji ILIKE.

Mencari % atau _ secara harfiah

Backslash meng-escape karakter berikutnya dalam pola, jadi '%50\%%' menemukan teks yang berisi 50%. Saat backslash merupakan bagian dari data, pilih karakter escape lain dengan ESCAPE:

SAVE\_% hanya cocok dengan SAVE_10. Tanpa escape, _ juga akan cocok dengan 1 di SAVE10.

Regular expression dengan ~

Operator regex-nya adalah ~ (cocok), ~* (cocok, tanpa memedulikan huruf besar kecil), !~ dan !~* (tidak cocok). Regex cocok di bagian mana pun dari string, kecuali kamu menambatkannya dengan ^ dan $:

Hanya 555-0101 yang cocok dengan format bertanda hubung. regexp_replace dengan flag g menghapus setiap karakter yang bukan digit, jadi (555) 0199 menjadi 5550199 dan call me menjadi string kosong.

Mengambil dan menghitung kecocokan

Fungsi regex lainnya mengembalikan bagian yang cocok, bukan true atau false:

FungsiMengembalikan
substring(s from 'pattern')Kecocokan pertama, atau grup pertama dalam tanda kurung
regexp_match(s, 'pattern')Grup dari kecocokan pertama sebagai text[]
regexp_matches(s, 'pattern', 'g')Satu baris per kecocokan
regexp_count(s, 'pattern')Jumlah kecocokan (sejak PostgreSQL 15)
regexp_substr(s, 'pattern')Kecocokan pertama (sejak PostgreSQL 15)
regexp_like(s, 'pattern')Sama dengan ~, dalam bentuk fungsi (sejak PostgreSQL 15)
regexp_split_to_table(s, 'pattern')Satu baris per potongan di antara kecocokan

substring('Order 4521 shipped' from '\d+') mengembalikan 4521. Bab pattern matching di manual memuat daftar flag dan sintaks regex.

Membuat pencarian pola memakai index

Index B-tree bisa melayani pola yang ditambatkan di awal, seperti LIKE 'abc%', saat database memakai collation C atau index-nya dibuat dengan text_pattern_ops:

CREATE INDEX users_email_prefix ON users (email text_pattern_ops);

Pola yang diawali %, ILIKE, dan sebagian besar regex tidak bisa memakai B-tree. Untuk itu, extension pg_trgm menambahkan index trigram yang melayani LIKE, ILIKE, ~, dan ~*:

CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);

Untuk kesamaan case-insensitive, bukan pola, index pada lower(email) yang di-query dengan lower(email) = 'ben@work.io' adalah pilihan yang lebih sederhana. Halaman fungsi string membahas lower, position, dan split_part.

Pertanyaan yang sering diajukan

Bagaimana cara mengecek apakah string mengandung substring di PostgreSQL?

Pakai col LIKE '%text%', atau col ILIKE '%text%' untuk mengabaikan huruf besar kecil. position('text' in col) > 0 dan strpos(col, 'text') > 0 juga bisa dan tidak perlu escape untuk % atau _.

Apakah LIKE case-sensitive di PostgreSQL?

Ya. 'Apple' LIKE 'a%' bernilai false. Pakai ILIKE, bandingkan lower(col) LIKE 'a%', atau pakai operator regex case-insensitive ~*.

Bagaimana cara mencocokkan beberapa pola sekaligus?

Gabungkan LIKE ANY dengan array: name LIKE ANY (ARRAY['a%', 'b%']) bernilai true jika salah satu pola cocok, dan LIKE ALL mengharuskan semuanya cocok. Alternasi regex seperti name ~ '^(a|b)' melakukan hal yang sama.

Apa itu SIMILAR TO di PostgreSQL?

Campuran LIKE dan regular expression dari standar SQL: memakai % dan _ plus |, *, +, dan grup, dan harus cocok dengan seluruh string. LIKE atau operator ~ biasanya lebih jelas.

Ilustrasi bahasa pemrograman di Coddy

Belajar coding dengan Coddy

Mulai