So verwendest du die SQL-Funktion REPLACE()

Kurz & knapp

• REPLACE(string, old_substring, new_substring) tauscht alle Vorkommen einer Teilzeichenfolge in einem einzigen Aufruf aus

• Mehrere Ersetzungen durch Verschachteln: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Leeren String übergeben, um Zeichen zu entfernen: REPLACE(phone, '-', '')

• Wenn ein Argument NULL ist, gibt die Funktion für diese Zeile NULL zurück

• Die Groß-/Kleinschreibung hängt von Datenbank und Kollation ab—SQL Server und MySQL sind standardmäßig nicht case-sensitiv, PostgreSQL ist standardmäßig case-sensitiv

Die SQL-Funktion REPLACE() verstehen

Die SQL-Funktion REPLACE() sucht in einer Spalte nach einer angegebenen Teilzeichenfolge bzw. einem String und ersetzt sie durch einen anderen String. Das Beispiel unten zeigt die grundlegende Verwendung von REPLACE(). Die Argumente der Funktion sind:

• string: Der Ursprungstext, in dem der Austausch stattfinden soll.

• old_substring: Die zu ersetzende Teilzeichenfolge.

• new_substring: Die Teilzeichenfolge, die die alte ersetzt.

REPLACE(string, old_substring, new_substring)

Wenn du REPLACE() verwendest, um Strings in einer Tabelle zu verändern, hat die Abfrage die folgende Syntax. Dabei gilt:

• column_name: Die Spalte, in der nach Vorkommen gesucht wird.

• old_substring: Die zu ersetzende Teilzeichenfolge.

• new_substring: Die neue Teilzeichenfolge.

-- Wähle die Spalte description aus der Tabelle products aus
-- und ersetze alle Vorkommen von 'old' durch 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Beispiel für REPLACE()

Die SQL-Funktion REPLACE() ersetzt alle Vorkommen eines Strings durch eine angegebene Teilzeichenfolge oder einen String. Im Beispiel unten ersetzt REPLACE() den Wert old durch new in der Spalte description. Die Abfrage läuft zeilenweise und tauscht den alten String jeweils aus.

-- Wähle die Spalte description aus der Tabelle products aus
-- und ersetze alle Vorkommen von 'old' durch 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Hinweis: MySQL, PostgreSQL, Oracle und SQL Server unterstützen alle die Funktion REPLACE().

Anwendungsfälle von REPLACE() in SQL

Es gibt viele wichtige Anwendungsfälle für REPLACE() bei der Textmanipulation und Datentransformation. Dazu zählen:

Einfacher Ersatz

Mit REPLACE() kannst du einfache Ersetzungen durchführen. Nehmen wir an, wir haben die folgende Tabelle in unserer SQL-Server-Datenbank:

Beispieltabelle Products. Bild: Autorin/Autor.

Mit der folgenden SQL-Abfrage änderst du den Status old in new mithilfe von REPLACE().

SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Die resultierende Tabelle sieht dann wie unten aus.

Tabelle aktualisiert mit SQL REPLACE(). Bild: Autorin/Autor.

Mehrere Teilzeichenfolgen ersetzen

Wenn du mehrere Teilzeichenfolgen ersetzen willst, verschachtle mehrere REPLACE()-Aufrufe. Hier ersetzen wir old durch outdated und new durch fresh.

-- Mehrere REPLACE()-Aufrufe verschachteln, um 'old' durch 'outdated' und 'new' durch 'fresh' zu ersetzen
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

Das Ergebnis siehst du unten.

Tabelle aktualisiert mit mehreren Ersetzungen. Bild: Autorin/Autor.

Zeichen entfernen

Übergebe einen leeren String als drittes Argument, um Zeichen vollständig zu entfernen. So lassen sich Satzzeichen, überflüssige Leerzeichen oder Formatierungszeichen am saubersten aus einer Spalte strippen.

-- Bindestriche aus Telefonnummern entfernen
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

Zum Beispiel wird aus '555-867-5309' '5558675309'. Mit derselben Technik entfernst du Sonderzeichen, Kodierungsartefakte oder unerwünschte Leerzeichen aus importierten Daten.

Groß-/Kleinschreibung behandeln

Die Case-Sensitivity von REPLACE() hängt von deiner Datenbank und ihrer Kollation ab. PostgreSQL ist standardmäßig case-sensitiv, daher matcht REPLACE(col, 'Old', 'New') nicht 'old'. SQL Server und MySQL folgen der Kollation der Spalte oder Datenbank—in den Standardeinstellungen meist nicht case-sensitiv, daher matcht REPLACE() unabhängig von der Schreibweise.

Wenn du schreibungsunabhängig ersetzen willst, konvertiere den Text vorab mit UPPER() oder LOWER(). So bleiben Werte konsistent.

-- 'OLD' durch 'OUTDATED' ersetzen und Case über UPPER behandeln
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

Tabelle aktualisiert mit REPLACE() und UPPER(). Bild: Autorin/Autor.

-- 'old' durch 'outdated' ersetzen und Case über LOWER behandeln
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

Tabelle aktualisiert mit SQL REPLACE() und LOWER(). Bild: Autorin/Autor.

Fortgeschrittene Techniken

Diese Techniken kombinieren REPLACE() mit anderen SQL-Funktionen, um Ersetzungen jenseits eines einfachen Suchen-und-Ersetzen abzudecken.

Mit dynamischen Werten ersetzen

Du kannst REPLACE() für dynamische Ersetzungen mit anderen SQL-Funktionen kombinieren. Zum Beispiel lässt sich der Wert einer anderen Spalte als Ersatz nutzen. Im Beispiel unten ersetzt REPLACE() jedes old durch den tatsächlichen Wert aus product_name.

-- 'old' durch den Wert aus der Spalte product_name ersetzen
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Tabelle aktualisiert mit REPLACE() und dynamischen Werten. Bild: Autorin/Autor.

REPLACE() in UPDATE-Anweisungen verwenden

REPLACE() funktioniert auch in UPDATE-Anweisungen, um Spaltenwerte über mehrere Zeilen zu ändern. Die Abfrage unten ersetzt old durch outdated in der Spalte status.

-- Spalte status aktualisieren: 'old' durch 'outdated' ersetzen
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Für komplexere Fälle, z. B. Updates per Join, sieh dir unser Tutorial zu SQL UPDATE mit JOIN an.

REPLACE() mit anderen Stringfunktionen kombinieren

Eine fortgeschrittene Technik ist die Kombination von REPLACE() mit anderen SQL-Stringfunktionen wie CHARINDEX() oder SUBSTRING(), wenn Teilstrings ersetzt werden.

-- Den Teil des Produktnamens ab dem ersten Leerzeichen durch ' Updated' ersetzen
SELECT 
    product_id,
    product_name,
    price,
    -- Den Teilstring von product_name ab dem ersten Leerzeichen bis zum Ende extrahieren
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

Die resultierende Tabelle der obigen Abfrage ist unten dargestellt.

Tabelle aktualisiert mit REPLACE() und weiteren Stringfunktionen. Bild: Autorin/Autor.

NULL durch 0 ersetzen

Du kannst die Funktion COALESCE() nutzen, um NULL vor dem Aufruf von REPLACE() durch einen Fallback zu ersetzen, wie unten gezeigt.

-- NULL in der Spalte status durch 'new' ersetzen
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

Stolperstein: Wenn irgendein Argument an REPLACE() NULL ist, gibt die Funktion für diese Zeile NULL zurück—selbst wenn die anderen Argumente gültig sind. Umhülle potenziell leere Spalten mit COALESCE() vor dem Aufruf von REPLACE(), nicht danach.

Praktische Einsatzszenarien für REPLACE()

Hier sind typische Szenarien, in denen REPLACE() in Produktivabfragen punktet.

• Datenbereinigung: REPLACE() hilft, unerwünschte Zeichen oder Leerzeichen in Strings zu entfernen bzw. zu ersetzen.

• Datentransformation: REPLACE() wird genutzt, um Werte in andere Formate zu überführen und veraltete Werte durch neue zu ersetzen.

• Umgang mit Nutzereingaben: REPLACE() vereinheitlicht und konvertiert Eingabedaten auf einen in der Datenbank akzeptierten Standard. So bleibt der Datenbestand konsistent.

• Textstandardisierung: REPLACE() standardisiert Werte in Textspalten, etwa Abkürzungen in durchgängig Klein- oder Großschreibung.

• Bedingte Ersetzung: REPLACE() unterstützt einfache Regeln, um Werte gemäß definierten Bedingungen auszutauschen.

Datenbankspezifische Implementierungen von REPLACE()

MySQL, PostgreSQL, Oracle und SQL Server unterstützen alle die SQL-Funktion REPLACE() mit einer ähnlichen Syntax.

-- 'old' durch 'new' in der Spalte status ersetzen
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Performance und Best Practices für REPLACE()

Beim Einsatz von REPLACE() solltest du folgende Performanceaspekte und Best Practices beachten.

Auswirkung auf die Abfrageleistung

REPLACE() kann die Abfrageleistung beeinträchtigen, vor allem bei großen Datenmengen. Das liegt daran, dass die Funktion für jede Zeile verarbeitet wird und die Ausführung entsprechend dauern kann.

Indexierung beachten

Indiziere die Spalte, gegen die du in WHERE oder UPDATE mit REPLACE() prüfst. Ohne Index muss die Datenbank jede Zeile scannen—bei großen Tabellen besonders teuer.

Engpässe überwachen und beheben

Um Leistungsprobleme zu adressieren, beachte diese Best Practices:

• Nutze Datenbank-Profiler, um Abfragen zu überwachen und bei großen Datenmengen zu optimieren.

• Wenn du Stringwerte mit REPLACE() änderst, pflege Indizes regelmäßig, damit sie aktuell bleiben.

• Setze Batchverarbeitung ein, um die Ausführungszeit bei sehr großen Datenmengen zu reduzieren.

Wenn du weitere fortgeschrittene SQL-Funktionen für Datentransformation lernen willst, empfehle ich den DataCamp-Kurs Learn SQL. Der Kurs Reporting in SQL behandelt außerdem Themen zur Datenbankoptimierung und zeigt Techniken zur Performanceverbesserung.

Fazit

REPLACE() deckt die meisten Anforderungen an Textersetzungen in SQL-Datenbanken ab. Behalte das Verhalten bei NULL im Kopf—jedes NULL-Argument führt zu NULL—und prüfe die Kollation deiner Datenbank, wenn es auf Groß-/Kleinschreibung ankommt.

Mehr über REPLACE() in SQL lernst du in den DataCamp-Kursen Intermediate SQL und SQL Fundamentals. Außerdem empfehle ich den Karrierepfad Associate Data Analyst in SQL, um die Anwendung von SQL in der Datentransformation zu vertiefen und deine Karriere zu beschleunigen. Abschließend lohnt sich die SQL Associate Certification von DataCamp, um deine SQL-Expertise sichtbar zu machen und im Bewerbungsgespräch zu überzeugen.

Wenn du dich für weitere SQL-Funktionen in der Datenanalyse interessierst, schau dir auch diese Tutorials an:

• How to Use the SQL BETWEEN Operator
• How to Use the SQL LIMIT Clause
• How to Use the SQL IN Operator
• How to Use the SQL OFFSET Clause