De SQL-functie REPLACE() gebruiken

TL;DR

• REPLACE(string, old_substring, new_substring) vervangt in één keer elke voorkomst van een substring

• Koppel aanroepen om meerdere substrings te vervangen: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Geef een lege string door om tekens te verwijderen: REPLACE(phone, '-', '')

• Als een van de argumenten NULL is, geeft de functie voor die rij NULL terug

• Hoofdlettergevoeligheid hangt af van je database en collation—SQL Server en MySQL zijn standaard niet hoofdlettergevoelig; PostgreSQL is standaard wél hoofdlettergevoelig

De SQL-functie REPLACE() begrijpen

De functie REPLACE() in SQL zoekt naar de opgegeven substring of string in een kolom en vervangt die door een andere opgegeven string. Het onderstaande voorbeeld toont het basale gebruik van de functie REPLACE(). De argumenten van de functie zijn:

• string: De oorspronkelijke string waarin je de vervanging maakt.

• old_substring: De te vervangen substring.

• new_substring: De substring die de oude vervangt.

REPLACE(string, old_substring, new_substring)

Als je de functie REPLACE() gebruikt om strings in een tabel te wijzigen, heeft de query de volgende syntaxis, waarin:

• column_name: De kolom waarin je de voorkomens van de substring zoekt.

• old_substring: De te vervangen substring.

• new_substring: De substring die de oude vervangt.

-- Selecteer de kolom description uit de tabel products,
-- waarbij voorkomens van 'old' worden vervangen door 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Voorbeeld van REPLACE()

De SQL-functie REPLACE() wijzigt of vervangt alle stringvoorkomens door een opgegeven substring of string. In het onderstaande voorbeeld heeft de SQL-functie REPLACE() de waarde old vervangen door new in de kolom description. De query doorloopt elke rij om de oude string door een nieuwe te vervangen.

-- Selecteer de kolom description uit de tabel products,
-- waarbij voorkomens van 'old' worden vervangen door 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Let op: MySQL-, PostgreSQL-, Oracle- en SQL Server-databases ondersteunen allemaal de functie REPLACE().

Use-cases van REPLACE() in SQL

Er zijn veel belangrijke use-cases voor de SQL-functie REPLACE() bij tekstmanipulatie en datatransformatie. Enkele voorbeelden:

Eenvoudige vervanging

Je kunt de SQL-functie REPLACE() gebruiken voor een eenvoudige vervanging. Stel dat we de volgende tabel hebben in onze SQL Server-database:

Voorbeeld van de tabel Products. Afbeelding door de auteur.

Met de volgende SQL-query kun je de functie REPLACE() gebruiken om de status old te veranderen in new.

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

De resulterende tabel ziet eruit zoals hieronder.

Tabel bijgewerkt met SQL REPLACE(). Afbeelding door de auteur.

Meerdere substrings vervangen

Als je meerdere substrings wilt vervangen, koppel je meerdere REPLACE()-functies. Hier werken we old bij naar outdated, en new naar fresh.

-- Koppel meerdere REPLACE()-functies om 'old' te vervangen door 'outdated' en 'new' door 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

De resulterende tabel staat hieronder.

Tabel bijgewerkt met meerdere substrings. Afbeelding door de auteur.

Tekens verwijderen

Geef een lege string door als derde argument om tekens volledig te verwijderen. Dit is de schoonste manier om interpunctie, extra spaties of opmaaktekens uit een kolom te strippen.

-- Streepjes uit telefoonnummers verwijderen
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

Zo wordt bijvoorbeeld '555-867-5309' '5558675309'. Met dezelfde techniek verwijder je speciale tekens, coderingsartefacten of ongewenste witruimte uit geïmporteerde data.

Omgaan met hoofdlettergevoeligheid

Hoofdlettergevoeligheid in REPLACE() hangt af van je database en collation. PostgreSQL behandelt dit standaard als hoofdlettergevoelig, dus REPLACE(col, 'Old', 'New') komt niet overeen met 'old'. SQL Server en MySQL volgen de collation van de kolom of database—de meeste standaardinstallaties gebruiken een niet-hoofdlettergevoelige collation, dus REPLACE() komt dan overeen ongeacht de case.

Als je waarden wilt vervangen zonder op hoofdletters te letten, gebruik dan de functies UPPER() of LOWER() om de tekst vóór de vervanging te converteren. Deze techniek houdt waarden consistent.

-- Vervang 'OLD' door 'OUTDATED' en behandel hoofdlettergevoeligheid door naar hoofdletters te converteren
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

Tabel bijgewerkt met de functies REPLACE() en UPPER(). Afbeelding door de auteur.

-- Vervang 'old' door 'outdated' en behandel hoofdlettergevoeligheid door naar kleine letters te converteren
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

Tabel bijgewerkt met de SQL-functies REPLACE() en LOWER(). Afbeelding door de auteur.

Geavanceerde technieken

Deze technieken combineren REPLACE() met andere SQL-functies om vervangingen af te handelen die verder gaan dan simpel zoeken-en-vervangen.

Vervangen met dynamische waarden

De functie REPLACE() kan worden gebruikt voor dynamische vervangingen in combinatie met andere SQL-functies. Zo kun je met de functie REPLACE() de waarde van een andere kolom gebruiken om de substring te vervangen. In het onderstaande voorbeeld vervangt de functie REPLACE() elk oud product door de daadwerkelijke waarde uit product_name.

-- Vervang 'old' door de waarde uit de kolom product_name
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Tabel bijgewerkt met de functie REPLACE() en dynamische waarden. Afbeelding door de auteur.

REPLACE() gebruiken in UPDATE-statements

De SQL-functie REPLACE() werkt binnen UPDATE-statements om kolomwaarden te wijzigen in één of meerdere rijen. De onderstaande query werkt de waarde old bij naar outdated in de kolom status.

-- Werk de kolom status bij door 'old' te vervangen door 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Voor complexere scenario’s, zoals het updaten van een tabel door deze te joinen met een andere, bekijk onze tutorial over SQL UPDATE with JOIN.

REPLACE() combineren met andere stringfuncties

Een geavanceerde techniek is het combineren van REPLACE() met andere SQL-stringfuncties, zoals CHARINDEX() of SUBSTRING(), bij het vervangen van stringwaarden.

-- Vervang een deel van de productnaam vanaf de eerste spatie door 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extraheer de substring van product_name vanaf de eerste spatie tot het einde
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

De resulterende tabel van de bovenstaande query staat hieronder.

Tabel bijgewerkt met REPLACE() en andere stringfuncties. Afbeelding door de auteur.

NULL vervangen door 0

Je kunt de functie COALESCE() gebruiken om de waarde NULL te vervangen door een fallback vóór je REPLACE() aanroept, zoals hieronder getoond.

-- Vervang NULL door 'new' in de kolom status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

Valkuil: Als een van de argumenten die aan REPLACE() worden doorgegeven NULL is, geeft de functie voor die rij NULL terug—zelfs als de andere argumenten geldig zijn. Wikkel kolommen die NULL kunnen zijn in COALESCE() vóór je REPLACE() aanroept, niet erna.

Praktische toepassingen van de SQL-functie REPLACE()

Hier zijn scenario’s waarin REPLACE() zijn plek in productiequeries verdient.

• Opschonen van data: De functie REPLACE() is handig voor data-opschoning: ongewenste tekens of spaties in strings binnen een dataset verwijderen en vervangen.

• Datatransformatie: De functie REPLACE() wordt gebruikt om waarden naar andere formaten om te zetten en verouderde waarden te vervangen door nieuwe.

• Gebruikersinvoer verwerken: De functie REPLACE() is belangrijk om gebruikersinvoer aan te passen en te converteren naar een uniforme standaard die in een specifieke database acceptabel is. Deze techniek zorgt voor uniformiteit in de database.

• Tekststandaardisatie: De functie REPLACE() standaardiseert waarden in stringkolommen om uniformiteit te waarborgen, zoals afkortingen in kleine letters of hoofdletters.

• Conditionele vervanging: De functie REPLACE() is ook nuttig voor eenvoudig databasebeheer om waarden te vervangen volgens specifieke regels.

Database-specifieke implementaties van REPLACE()

MySQL-, PostgreSQL-, Oracle- en SQL Server-databases ondersteunen allemaal de functie REPLACE() in SQL, met een vergelijkbare syntaxis in deze databases.

-- Vervang 'old' door 'new' in de kolom status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

REPLACE(): prestatie-overwegingen en best practices

Bij het gebruik van de functie REPLACE() is het belangrijk om de volgende prestatie-overwegingen en best practices in gedachten te houden.

Impact op queryprestaties

De functie REPLACE() kan invloed hebben op de queryprestaties, vooral bij grote datasets. Dit komt doordat de functie de tabel per rij bijwerkt en het uitvoeren van de query daardoor langer kan duren.

Indexering

Voorzie de kolom waarop je zoekt van een index voordat je REPLACE() in een WHERE-clausule of UPDATE gebruikt. Zonder index scant de database elke rij om overeenkomsten te vinden—extra kostbaar bij grote tabellen.

Prestatieknelpunten monitoren en oplossen

Om prestatieproblemen op te lossen, houd rekening met de volgende best practices:

• Gebruik databaseprofilingtools om queryprestaties te monitoren en te optimaliseren bij grote datasets.

• Als je REPLACE() gebruikt om stringwaarden te wijzigen, onderhoud de indexen regelmatig zodat ze blijven aansluiten.

• Start batchverwerking om de uitvoeringstijd van queries te verkorten bij grote datasets.

Wil je andere geavanceerde SQL-functies voor datatransformatie leren, dan raad ik DataCamp’s cursus Learn SQL aan. De cursus Reporting in SQL behandelt ook database-optimalisatie en helpt je verschillende technieken te leren om prestaties te verbeteren.

Tot slot

De functie REPLACE() dekt de meeste behoeften aan tekstvervanging in SQL-databases. Let op het gedrag rond NULL—elk NULL-argument levert NULL op—en controleer de collation-instellingen van je database wanneer hoofdlettergevoeligheid ertoe doet.

Je kunt meer leren over de functie REPLACE() in SQL door DataCamp’s cursussen Intermediate SQL en SQL Fundamentals te volgen. Ik raad ook de career track Associate Data Analyst in SQL aan om meer te leren over toepassingen van SQL in datatransformatie en je carrière een vliegende start te geven. Tot slot moedig ik je aan om DataCamp’s SQL Associate Certification te behalen om je professionele vaardigheid in SQL voor data-analyse te tonen en op te vallen tijdens sollicitaties.

Als je meer wilt leren over verschillende SQL-functies die worden gebruikt in data-analyse, bekijk dan ook mijn andere tutorials, waaronder:

• 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