Menu

PostgreSQL LIKE, ILIKE und reguläre Ausdrücke

LIKE vergleicht Text mit einem Muster aus % und _, ILIKE macht dasselbe ohne Rücksicht auf Groß- und Kleinschreibung, und die ~ Operatoren prüfen POSIX-Regex.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

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:

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

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's