LIKE vergleicht Text mit einem Muster, in dem % für eine beliebige Zeichenfolge und _ für genau ein Zeichen steht. email LIKE '%@gmail.com' findet also Gmail-Adressen. LIKE unterscheidet Groß- und Kleinschreibung. ILIKE ist derselbe Vergleich ohne diese Unterscheidung, und ~ und ~* prüfen POSIX-Regex.
Die Abfrage liefert Asha und Bella: ILIKE ignoriert die Großbuchstaben in Bellas Adresse, und Chens .co passt nicht zur Endung.
Mit % und _ vergleichen
Der ganze String muss zum Muster passen, ein Muster ohne % wirkt also wie =. NOT LIKE und NOT ILIKE kehren den Vergleich um.
A-___ passt auf A-100, aber nicht auf A-2000, weil jedes _ für genau ein Zeichen steht. Das kleingeschriebene a-300 besteht nur den Test mit ILIKE.
Nach einem echten % oder _ suchen
Ein Backslash maskiert im Muster das nächste Zeichen, '%50\%%' findet also Text, der 50% enthält. Wenn Backslashes Teil der Daten sind, wählst du mit ESCAPE ein anderes Escape-Zeichen:
SAVE\_% passt nur auf SAVE_10. Ohne Escape würde _ auch auf die 1 in SAVE10 passen.
Reguläre Ausdrücke mit ~
Die Regex-Operatoren sind ~ (passt), ~* (passt, ohne Groß- und Kleinschreibung), !~ und !~* (passt nicht). Ein Regex passt überall im String, außer du verankerst ihn mit ^ und $:
Nur 555-0101 passt auf das Format mit Bindestrich. regexp_replace mit dem Flag g entfernt jedes Nicht-Ziffern-Zeichen, (555) 0199 wird also zu 5550199 und call me zu einem leeren String.
Treffer herausziehen und zählen
Andere Regex-Funktionen liefern den Treffer statt wahr oder falsch:
| Funktion | Liefert |
|---|---|
substring(s from 'pattern') | Den ersten Treffer oder die erste Gruppe in Klammern |
regexp_match(s, 'pattern') | Die Gruppen des ersten Treffers als text[] |
regexp_matches(s, 'pattern', 'g') | Eine Zeile pro Treffer |
regexp_count(s, 'pattern') | Die Anzahl der Treffer (seit PostgreSQL 15) |
regexp_substr(s, 'pattern') | Den ersten Treffer (seit PostgreSQL 15) |
regexp_like(s, 'pattern') | Dasselbe wie ~, als Funktion (seit PostgreSQL 15) |
regexp_split_to_table(s, 'pattern') | Eine Zeile pro Stück zwischen den Treffern |
substring('Order 4521 shipped' from '\d+') liefert 4521. Das Kapitel zur Mustersuche im Handbuch listet die Flags und die Regex-Syntax auf.
Mustersuchen einen Index nutzen lassen
Ein B-Tree-Index kann ein am Anfang verankertes Muster wie LIKE 'abc%' bedienen, wenn die Datenbank die Collation C nutzt oder der Index mit text_pattern_ops gebaut ist:
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
Ein Muster, das mit % beginnt, ILIKE und die meisten Regex können keinen B-Tree nutzen. Für diese Fälle bringt die Erweiterung pg_trgm Trigramm-Indizes, die LIKE, ILIKE, ~ und ~* bedienen:
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
Für Gleichheit ohne Groß- und Kleinschreibung statt Mustern ist ein Index auf lower(email), abgefragt mit lower(email) = 'ben@work.io', die einfachere Wahl. Die Seite zu String-Funktionen behandelt lower, position und split_part.
Häufig gestellte Fragen
Wie prüfe ich in PostgreSQL, ob ein String einen Teilstring enthält?
Mit col LIKE '%text%' oder col ILIKE '%text%', wenn Groß- und Kleinschreibung egal sein soll. position('text' in col) > 0 und strpos(col, 'text') > 0 funktionieren auch und brauchen kein Escaping von % oder _.
Unterscheidet LIKE in PostgreSQL Groß- und Kleinschreibung?
Ja. 'Apple' LIKE 'a%' ist falsch. Nimm ILIKE, vergleiche lower(col) LIKE 'a%' oder nutze den Regex-Operator ~*, der Groß- und Kleinschreibung ignoriert.
Wie prüfe ich mehrere Muster auf einmal?
Kombiniere LIKE ANY mit einem Array: name LIKE ANY (ARRAY['a%', 'b%']) ist wahr, wenn ein Muster passt, und LIKE ALL verlangt, dass jedes passt. Eine Regex-Alternative wie name ~ '^(a|b)' macht dasselbe.
Was ist SIMILAR TO in PostgreSQL?
Eine Mischung aus LIKE und regulären Ausdrücken aus dem SQL-Standard: Sie nutzt % und _ plus |, *, + und Gruppen und muss den ganzen String treffen. LIKE oder die ~ Operatoren sind meist klarer.