Menu

PostgreSQL LIKE, ILIKE i wyrażenia regularne

LIKE porównuje tekst ze wzorcem z % i _, ILIKE robi to samo bez rozróżniania wielkości liter, a operatory ~ dopasowują wyrażenia regularne POSIX.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

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:

FunkcjaZwraca
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.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij