LIKE porównuje tekst ze wzorcem, w którym % pasuje do dowolnego ciągu znaków, a _ do dokładnie jednego, więc email LIKE '%@gmail.com' znajduje adresy Gmail. LIKE rozróżnia wielkość liter. ILIKE to to samo dopasowanie bez rozróżniania wielkości liter, a ~ i ~* dopasowują wyrażenia regularne POSIX.
Zapytanie zwraca Asha i Bella: ILIKE ignoruje wielkie litery w adresie Belli, a .co Chena nie pasuje do końcówki.
Dopasowanie przez % i _
Cały tekst musi pasować do wzorca, więc wzorzec bez % działa jak =. NOT LIKE i NOT ILIKE odwracają dopasowanie.
A-___ pasuje do A-100, ale nie do A-2000, bo każdy _ oznacza dokładnie jeden znak. Pisane małymi literami a-300 przechodzi tylko test z ILIKE.
Szukanie dosłownego % albo _
Ukośnik wsteczny escapuje następny znak we wzorcu, więc '%50\%%' znajduje tekst zawierający 50%. Gdy ukośniki są częścią danych, wybierz inny znak escape przez ESCAPE:
SAVE\_% pasuje tylko do SAVE_10. Bez escapowania _ pasowałby też do 1 w SAVE10.
Wyrażenia regularne przez ~
Operatory regex to ~ (pasuje), ~* (pasuje bez rozróżniania wielkości liter), !~ i !~* (nie pasuje). Wyrażenie regularne pasuje w dowolnym miejscu tekstu, chyba że zakotwiczysz je przez ^ i $:
Tylko 555-0101 pasuje do formatu z myślnikiem. regexp_replace z flagą g usuwa każdy znak niebędący cyfrą, więc (555) 0199 staje się 5550199, a call me pustym tekstem.
Wyciąganie i liczenie dopasowań
Inne funkcje regex zwracają dopasowanie zamiast prawdy albo fałszu:
| Funkcja | Zwraca |
|---|---|
substring(s from 'pattern') | Pierwsze dopasowanie albo pierwszą grupę w nawiasach |
regexp_match(s, 'pattern') | Grupy pierwszego dopasowania jako text[] |
regexp_matches(s, 'pattern', 'g') | Jeden wiersz na dopasowanie |
regexp_count(s, 'pattern') | Liczbę dopasowań (od PostgreSQL 15) |
regexp_substr(s, 'pattern') | Pierwsze dopasowanie (od PostgreSQL 15) |
regexp_like(s, 'pattern') | To samo co ~, jako funkcja (od PostgreSQL 15) |
regexp_split_to_table(s, 'pattern') | Jeden wiersz na fragment między dopasowaniami |
substring('Order 4521 shipped' from '\d+') zwraca 4521. Rozdział o dopasowywaniu wzorców w dokumentacji wymienia flagi i składnię wyrażeń regularnych.
Wyszukiwanie wzorców z użyciem indeksu
Indeks B-tree obsłuży wzorzec zakotwiczony na początku, na przykład LIKE 'abc%', gdy baza używa porządku sortowania C albo indeks zbudowano z text_pattern_ops:
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
Wzorzec zaczynający się od %, ILIKE i większość wyrażeń regularnych nie mogą użyć B-tree. Do nich rozszerzenie pg_trgm dodaje indeksy trigramowe obsługujące LIKE, ILIKE, ~ i ~*:
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
Do porównania równości bez rozróżniania wielkości liter, a nie wzorców, prostszym wyborem jest indeks na lower(email) odpytywany przez lower(email) = 'ben@work.io'. Strona o funkcjach tekstowych omawia lower, position i split_part.
Najczęściej zadawane pytania
Jak sprawdzić w PostgreSQL, czy tekst zawiera podciąg?
Użyj col LIKE '%text%' albo col ILIKE '%text%', żeby pominąć wielkość liter. position('text' in col) > 0 i strpos(col, 'text') > 0 też działają i nie wymagają escapowania % ani _.
Czy LIKE w PostgreSQL rozróżnia wielkość liter?
Tak. 'Apple' LIKE 'a%' jest fałszem. Użyj ILIKE, porównaj lower(col) LIKE 'a%' albo użyj operatora regex ~*, który ignoruje wielkość liter.
Jak dopasować kilka wzorców naraz?
Połącz LIKE ANY z tablicą: name LIKE ANY (ARRAY['a%', 'b%']) jest prawdą, gdy pasuje którykolwiek wzorzec, a LIKE ALL wymaga, żeby pasowały wszystkie. Alternatywa w wyrażeniu regularnym, na przykład name ~ '^(a|b)', robi to samo.
Czym jest SIMILAR TO w PostgreSQL?
To ze standardu SQL połączenie LIKE i wyrażeń regularnych: używa % i _ oraz |, *, + i grup, i musi dopasować cały tekst. LIKE albo operatory ~ są zwykle czytelniejsze.