Så använder du SQL-funktionen REPLACE()

TL;DR

• REPLACE(string, old_substring, new_substring) byter ut varje förekomst av en delsträng i ett enda anrop

• Kedja anrop för att ersätta flera delsträngar: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Skicka en tom sträng för att ta bort tecken: REPLACE(phone, '-', '')

• Om något argument är NULL returnerar funktionen NULL för den raden

• Skiftlägeskänslighet beror på din databas och kollation—SQL Server och MySQL är som standard skiftlägesokänsliga; PostgreSQL är som standard skiftlägeskänsligt

Förstå SQL-funktionen REPLACE()

Funktionen REPLACE() i SQL söker efter en angiven delsträng eller sträng i en kolumn och ersätter den med en annan angiven sträng. Exemplet nedan visar grundanvändningen av funktionen REPLACE(). Argumenten i funktionen är:

• string: Den ursprungliga strängen där du ska göra ersättningen.

• old_substring: Delsträngen som ska ersättas.

• new_substring: Delsträngen som ska ersätta den gamla.

REPLACE(string, old_substring, new_substring)

När du använder funktionen REPLACE() för att ändra strängarna i en tabell får frågan följande syntax, där:

• column_name: Kolumnen som används för att söka efter förekomster av delsträngen.

• old_substring: Delsträngen som ska ersättas.

• new_substring: Delsträngen som ska ersätta den gamla.

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Exempel på REPLACE()

SQL-funktionen REPLACE() ändrar eller ersätter alla strängförekomster med en angiven delsträng eller sträng. I exemplet nedan har funktionen REPLACE() i SQL ersatt värdet old med new i kolumnen description. Frågan kör varje rad för att byta ut den gamla strängen mot en ny.

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Obs: MySQL, PostgreSQL, Oracle och SQL Server stöder alla funktionen REPLACE().

Användningsfall för REPLACE() i SQL

Det finns många viktiga användningsfall för SQL-funktionen REPLACE() inom textmanipulation och datatransformation. De inkluderar följande:

Grundläggande ersättning

Du kan använda SQL-funktionen REPLACE() för att göra en grundläggande ersättning. Anta att vi har följande tabell i vår SQL Server-databas:

Exempel på tabellen Products. Bild av författaren.

Med följande SQL-fråga kan du använda funktionen REPLACE() för att ändra status old till new.

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

Den resulterande tabellen kommer att se ut som den som visas nedan.

Tabell uppdaterad med SQL REPLACE(). Bild av författaren.

Ersätta flera delsträngar

Om du vill ersätta flera delsträngar använder du kedjade REPLACE()-funktioner. Här uppdaterar vi värdet old till outdated och new till fresh.

-- Chain multiple REPLACE() functions to replace 'old' with 'outdated' and 'new' with 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

Den resulterande tabellen visas nedan.

Tabell uppdaterad med flera delsträngar. Bild av författaren.

Ta bort tecken

Skicka en tom sträng som det tredje argumentet för att ta bort tecken helt. Det här är det renaste sättet att rensa bort skiljetecken, extra mellanslag eller formateringstecken från en kolumn.

-- Remove dashes from phone numbers
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

Till exempel blir '555-867-5309' '5558675309'. Samma teknik tar bort specialtecken, kodningsartefakter eller oönskade blanksteg från importerad data.

Hantering av skiftlägeskänslighet

Skiftlägeskänslighet i REPLACE() beror på din databas och kollation. PostgreSQL behandlar den som skiftlägeskänslig som standard, så REPLACE(col, 'Old', 'New') matchar inte 'old'. SQL Server och MySQL följer kolumnens eller databasens kollation—de flesta standardinstallationer använder skiftlägesokänslig kollation, så REPLACE() matchar oavsett skiftläge.

När du vill ersätta värden utan hänsyn till skiftläge bör du använda funktionerna UPPER() eller LOWER() för att konvertera texten före ersättning. Den här tekniken håller värden konsekventa.

-- Replace 'OLD' with 'OUTDATED', handling case sensitivity by converting to upper case
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

Tabell uppdaterad med funktionerna REPLACE() och UPPER(). Bild av författaren.

-- Replace 'old' with 'outdated', handling case sensitivity by converting to lowercase
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

Tabell uppdaterad med SQL-funktionerna REPLACE() och LOWER(). Bild av författaren.

Avancerade tekniker

Dessa tekniker kombinerar REPLACE() med andra SQL-funktioner för att hantera ersättningar som går bortom enkel sök-och-ersätt.

Ersätta med dynamiska värden

Funktionen REPLACE() kan användas för att göra dynamiska ersättningar genom att kombinera den med andra SQL-funktioner. Till exempel kan du med hjälp av funktionen REPLACE() använda värdet i en annan kolumn för att ersätta delsträngen. I exemplet nedan ersätter funktionen REPLACE() varje gammal produkt med det faktiska värdet från product_name.

-- Replace 'old' with the value from product_name column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Tabell uppdaterad med funktionen REPLACE() och dynamiska värden. Bild av författaren.

Använda REPLACE() i UPDATE-satser

SQL-funktionen REPLACE() fungerar i UPDATE-satser för att ändra kolumnvärden över en eller flera rader. Frågan nedan uppdaterar värdet old till outdated i kolumnen status.

-- Update the status column to replace 'old' with 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

För mer komplexa scenarier, till exempel att uppdatera en tabell genom att joina den med en annan, se vår handledning om SQL UPDATE med JOIN.

Kombinera REPLACE() med andra strängfunktioner

En avancerad teknik är att kombinera REPLACE() med andra SQL-strängfunktioner, såsom CHARINDEX() eller SUBSTRING(), när du ersätter strängvärden.

-- Replace part of the product name starting from the first space with 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extract the substring of product_name starting from the first space to the end
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

Den resulterande tabellen från frågan ovan visas nedan.

Tabell uppdaterad med REPLACE() och andra strängfunktioner. Bild av författaren.

Ersätta NULL med 0

Du kan använda funktionen COALESCE() för att ersätta värdet NULL med en reserv innan du anropar REPLACE(), som visas nedan.

-- Replace NULL with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

Fallgrop: Om något argument som skickas till REPLACE() är NULL returnerar funktionen NULL för den raden—även om de andra argumenten är giltiga. Omslut kolumner som kan vara null i COALESCE() innan du anropar REPLACE(), inte efter.

Praktiska användningar av SQL-funktionen REPLACE()

Här är scenarier där REPLACE() förtjänar sin plats i produktionsfrågor.

• Datarensning: Funktionen REPLACE() är användbar för datarensning, borttagning och ersättning av oönskade tecken eller mellanslag i strängar i ett dataset.

• Datatransformation: Funktionen REPLACE() används i datatransformation för att ändra värden till andra format och ersätta föråldrade värden med nya.

• Hantering av användarinmatningar: Funktionen REPLACE() är viktig för att justera och konvertera användarinmatad data till en enhetlig standard som är godtagbar i en viss databas. Den här tekniken hjälper till att säkerställa enhetlighet i databasen.

• Textstandardisering: Funktionen REPLACE() standardiserar värden i strängkolumner för att säkerställa enhetlighet, till exempel förkortningar i gemener eller versaler.

• Villkorlig ersättning: Funktionen REPLACE() är också användbar för enklare databashantering för att ersätta värden enligt specifika regler.

Databasspecifika implementationer av REPLACE()

MySQL, PostgreSQL, Oracle och SQL Server stöder alla funktionen REPLACE() i SQL, som har liknande syntax i dessa databaser.

-- Replace 'old' with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Prestandaöverväganden och bästa praxis för REPLACE()

När du använder funktionen REPLACE() är det viktigt att notera följande prestandaöverväganden och bästa praxis.

Påverkan på frågeprestanda

Funktionen REPLACE() kan påverka frågeprestandan, särskilt när stora dataset hanteras. Detta beror på att funktionen uppdaterar tabellen för varje rad och det kan ta tid för frågan att köras.

Indexeringsöverväganden

Indexera kolumnen du frågar mot innan du kör REPLACE() i en WHERE-sats eller UPDATE. Utan index skannar databasen varje rad för att hitta matchningar—särskilt kostsamt på stora tabeller.

Övervakning och åtgärd av prestandaflaskhalsar

För att lösa problem kopplade till frågeprestanda är det viktigt att beakta följande bästa praxis:

• Använd databasprofileringsverktyg för att övervaka frågeprestanda och optimera vid hantering av stora dataset.

• När du använder REPLACE() för att ändra strängvärden, underhåll indexen regelbundet för att säkerställa att de stämmer.

• Initiera batchbearbetning för att minska körningstiden för frågor vid hantering av stora dataset.

För att lära dig andra avancerade SQL-funktioner för datatransformation rekommenderar jag DataCamps kurs Learn SQL. Kursen Reporting in SQL tar också upp ämnen om databaskoptimering och hjälper dig att lära dig olika tekniker för att optimera prestanda.

Avslutande tankar

Funktionen REPLACE() täcker de flesta behoven av textsubstitution i SQL-databaser. Ha beteendet för NULL i åtanke—alla NULL-argument returnerar NULL—och kontrollera din databas kollationsinställningar när skiftlägeskänslighet spelar roll.

Du kan lära dig mer om funktionen REPLACE() i SQL genom att gå DataCamps kurser Intermediate SQL och SQL Fundamentals. Jag rekommenderar också karriärspåret Associate Data Analyst in SQL för att lära dig mer om SQL:s användning i datatransformation och kickstarta din karriär. Slutligen uppmuntrar jag dig att ta DataCamps SQL Associate Certification för att visa upp dina professionella meriter i att använda SQL för dataanalys och sticka ut vid jobbintervjuer.

Om du är intresserad av att lära dig mer om olika SQL-funktioner som används i dataanalys rekommenderar jag att du kollar in några av mina andra handledningar, inklusive:

• Hur du använder SQL-operatorn BETWEEN
• Hur du använder SQL-satsen LIMIT
• Hur du använder SQL-operatorn IN
• Hur du använder SQL-satsen OFFSET