Come usare la funzione SQL REPLACE()

TL;DR

• REPLACE(string, old_substring, new_substring) sostituisce ogni occorrenza di una sottostringa in un'unica chiamata

• Concatena più chiamate per sostituire più sottostringhe: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Passa una stringa vuota per rimuovere caratteri: REPLACE(phone, '-', '')

• Se uno qualsiasi degli argomenti è NULL, la funzione restituisce NULL per quella riga

• La sensibilità al maiuscolo/minuscolo dipende dal database e dalla collation: SQL Server e MySQL sono per impostazione predefinita case-insensitive; PostgreSQL è per impostazione predefinita case-sensitive

Comprendere la funzione SQL REPLACE()

La funzione REPLACE() in SQL cerca la sottostringa o stringa specificata in una colonna e la sostituisce con un'altra stringa indicata. L'esempio seguente mostra l'uso di base della funzione REPLACE(). Gli argomenti della funzione includono:

• string: La stringa originale in cui devi effettuare la sostituzione.

• old_substring: La sottostringa da sostituire.

• new_substring: La sottostringa che sostituisce quella vecchia.

REPLACE(string, old_substring, new_substring)

Quando usi la funzione REPLACE() per modificare le stringhe in una tabella, la query avrà la seguente sintassi, in cui:

• column_name: La colonna su cui cercare le occorrenze della sottostringa.

• old_substring: La sottostringa da sostituire.

• new_substring: La sottostringa che sostituisce quella vecchia.

-- 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;

Esempio di REPLACE()

La funzione SQL REPLACE() modifica o sostituisce tutte le occorrenze di una stringa con una specifica sottostringa o stringa. Nell'esempio seguente, la funzione REPLACE() in SQL ha sostituito il valore old con new nella colonna description. La query verrà eseguita su ogni riga per sostituire la stringa vecchia con la nuova.

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

Nota: I database MySQL, PostgreSQL, Oracle e SQL Server supportano tutti la funzione REPLACE().

Casi d'uso di REPLACE() in SQL

Ci sono molti casi d'uso importanti della funzione SQL REPLACE() nella manipolazione del testo e nella trasformazione dei dati. Ecco i principali:

Sostituzione di base

Puoi usare la funzione SQL REPLACE() per eseguire una sostituzione di base. Supponiamo di avere la seguente tabella nel nostro database SQL Server:

Esempio di tabella Products. Immagine dell'autore.

Usando la seguente query SQL, puoi usare la funzione REPLACE() per cambiare lo stato old in new.

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

La tabella risultante sarà simile a quella mostrata di seguito.

Tabella aggiornata con SQL REPLACE(). Immagine dell'autore.

Sostituire più sottostringhe

Se vuoi sostituire più sottostringhe, usa più funzioni REPLACE() concatenate. Qui aggiorniamo il valore old con outdated e new con 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;

La tabella risultante è mostrata di seguito.

Tabella aggiornata usando più sottostringhe. Immagine dell'autore.

Rimuovere caratteri

Passa una stringa vuota come terzo argomento per rimuovere completamente i caratteri. È il modo più pulito per eliminare punteggiatura, spazi extra o caratteri di formattazione da una colonna.

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

Per esempio, '555-867-5309' diventa '5558675309'. La stessa tecnica rimuove caratteri speciali, artefatti di codifica o spazi indesiderati dai dati importati.

Gestire la distinzione tra maiuscole e minuscole

La sensibilità al maiuscolo/minuscolo in REPLACE() dipende dal tuo database e dalla collation. PostgreSQL la tratta come case-sensitive per impostazione predefinita, quindi REPLACE(col, 'Old', 'New') non corrisponderà a 'old'. SQL Server e MySQL seguono la collation della colonna o del database: la maggior parte delle installazioni predefinite usa una collation case-insensitive, quindi REPLACE() farà match indipendentemente dal caso.

Quando vuoi sostituire valori senza distinzione tra maiuscole e minuscole, usa le funzioni UPPER() o LOWER() per convertire il testo prima della sostituzione. Questa tecnica mantiene i valori coerenti.

-- 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;

Tabella aggiornata usando le funzioni REPLACE() e UPPER(). Immagine dell'autore.

-- 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;

Tabella aggiornata usando le funzioni SQL REPLACE() e LOWER(). Immagine dell'autore.

Tecniche avanzate

Queste tecniche combinano REPLACE() con altre funzioni SQL per gestire sostituzioni che vanno oltre un semplice trova-e-sostituisci.

Sostituzione con valori dinamici

La funzione REPLACE() può essere usata per eseguire sostituzioni dinamiche combinandola con altre funzioni SQL. Per esempio, usando la funzione REPLACE(), puoi utilizzare il valore di un'altra colonna per sostituire la sottostringa. Nell'esempio seguente, la funzione REPLACE() sostituisce ogni prodotto "old" con il valore effettivo di 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;

Tabella aggiornata usando la funzione REPLACE() e valori dinamici. Immagine dell'autore.

Usare REPLACE() nelle istruzioni UPDATE

La funzione SQL REPLACE() funziona all'interno delle istruzioni UPDATE per modificare i valori delle colonne su una o più righe. La query seguente aggiorna il valore old con outdated nella colonna status.

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

Per scenari più complessi, come l'aggiornamento di una tabella facendole join con un'altra, vedi il nostro tutorial su SQL UPDATE con JOIN.

Combinare REPLACE() con altre funzioni stringa

Una tecnica avanzata consiste nel combinare REPLACE() con altre funzioni stringa SQL, come CHARINDEX() o SUBSTRING(), quando si sostituiscono valori di stringa.

-- 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;

La tabella risultante dalla query sopra è mostrata di seguito.

Tabella aggiornata usando REPLACE() e altre funzioni stringa. Immagine dell'autore.

Sostituire NULL con 0

Puoi usare la funzione COALESCE() per sostituire il valore NULL con un fallback prima di chiamare REPLACE(), come mostrato di seguito.

-- 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;

Insidia: Se qualunque argomento passato a REPLACE() è NULL, la funzione restituisce NULL per quella riga, anche se gli altri argomenti sono validi. Incapsula le colonne che possono essere NULL in COALESCE() prima di chiamare REPLACE(), non dopo.

Applicazioni pratiche della funzione SQL REPLACE()

Ecco gli scenari in cui REPLACE() si guadagna un posto nelle query in produzione.

• Data cleaning: La funzione REPLACE() è utile per il data cleaning, rimuovendo e sostituendo caratteri o spazi indesiderati nelle stringhe all'interno di un dataset.

• Trasformazione dei dati: La funzione REPLACE() viene usata nella trasformazione dei dati per cambiare i valori in formati diversi e sostituire quelli obsoleti con nuovi.

• Gestione degli input utente: La funzione REPLACE() è importante per adattare e convertire i dati inseriti dagli utenti a uno standard uniforme accettabile in uno specifico database. Questa tecnica aiuta a garantire l'uniformità nel database.

• Standardizzazione del testo: La funzione REPLACE() standardizza i valori nelle colonne di tipo stringa per garantire uniformità, ad esempio abbreviazioni in formato minuscolo o maiuscolo.

• Sostituzione condizionale: La funzione REPLACE() è utile anche per pratiche di gestione del database semplificate per sostituire valori secondo regole specifiche.

Implementazioni specifiche per database di REPLACE()

I database MySQL, PostgreSQL, Oracle e SQL Server supportano tutti la funzione REPLACE() in SQL, che ha una sintassi simile in questi database.

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

Considerazioni sulle prestazioni e best practice per REPLACE()

Quando usi la funzione REPLACE(), è importante considerare le seguenti indicazioni sulle prestazioni e le best practice.

Impatto sulle prestazioni della query

La funzione REPLACE() può incidere sulle prestazioni della query, soprattutto con dataset di grandi dimensioni. Questo accade perché la funzione aggiorna la tabella per ogni riga e l'esecuzione della query può richiedere tempo.

Considerazioni sull'indicizzazione

Indicizza la colonna su cui effettui la query prima di eseguire REPLACE() in una WHERE o in un UPDATE. Senza un indice, il database esegue una scansione di ogni riga per trovare le corrispondenze, un'operazione particolarmente costosa su tabelle grandi.

Monitoraggio e risoluzione dei colli di bottiglia delle prestazioni

Per risolvere i problemi legati alle prestazioni delle query, considera le seguenti best practice:

• Usa strumenti di profiling del database per monitorare le prestazioni delle query e ottimizzare quando gestisci dataset di grandi dimensioni.

• Quando usi REPLACE() per modificare i valori stringa, mantieni regolarmente gli indici per assicurarti che siano coerenti.

• Avvia elaborazioni in batch per ridurre i tempi di esecuzione delle query quando gestisci grandi volumi di dati.

Per imparare altre funzioni SQL avanzate per la trasformazione dei dati, ti consiglio il corso di DataCamp Learn SQL. Il corso Reporting in SQL tratta anche argomenti sull'ottimizzazione dei database, aiutandoti a imparare diverse tecniche per migliorare le prestazioni.

Considerazioni finali

La funzione REPLACE() copre la maggior parte delle esigenze di sostituzione del testo nei database SQL. Tieni a mente il comportamento con i NULL: qualsiasi argomento NULL restituisce NULL, e verifica le impostazioni di collation del tuo database quando la distinzione tra maiuscole e minuscole è importante.

Puoi approfondire la funzione REPLACE() in SQL seguendo i corsi di DataCamp Intermediate SQL e SQL Fundamentals. Ti consiglio anche il career track Associate Data Analyst in SQL per scoprire di più sulle applicazioni di SQL nella trasformazione dei dati e dare slancio alla tua carriera. Infine, ti invito a sostenere la SQL Associate Certification di DataCamp per mettere in evidenza i tuoi risultati professionali nell'uso di SQL per l'analisi dei dati e distinguerti durante i colloqui.

Se vuoi approfondire altre funzioni SQL usate nell'analisi dei dati, ti consiglio anche questi miei tutorial:

• Come usare l'operatore SQL BETWEEN
• Come usare la clausola SQL LIMIT
• Come usare l'operatore SQL IN
• Come usare la clausola SQL OFFSET