Jak używać funkcji SQL REPLACE()

TL;DR

• REPLACE(string, old_substring, new_substring) podmienia każde wystąpienie podłańcucha w jednym wywołaniu

• Łącz wywołania, aby zastąpić wiele podłańcuchów: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Przekaż pusty łańcuch, aby usunąć znaki: REPLACE(phone, '-', '')

• Jeśli dowolny argument to NULL, funkcja zwraca NULL dla tego wiersza

• Wrażliwość na wielkość liter zależy od bazy danych i sortowania—SQL Server i MySQL domyślnie są niewrażliwe na wielkość liter; PostgreSQL domyślnie jest wrażliwy

Zrozumienie funkcji SQL REPLACE()

Funkcja REPLACE() w SQL wyszukuje wskazany podłańcuch lub łańcuch w kolumnie i zastępuje go innym podanym łańcuchem. Poniższy przykład pokazuje podstawowe użycie funkcji REPLACE(). Argumenty funkcji obejmują:

• string: Oryginalny łańcuch, w którym masz dokonać zamiany.

• old_substring: Podłańcuch do zastąpienia.

• new_substring: Podłańcuch, który zastępuje stary.

REPLACE(string, old_substring, new_substring)

Używając funkcji REPLACE() do zmiany łańcuchów w tabeli, zapytanie będzie miało następującą składnię, gdzie:

• column_name: Kolumna, w której wyszukujesz wystąpienia podłańcucha.

• old_substring: Podłańcuch do zastąpienia.

• new_substring: Podłańcuch, który zastępuje stary.

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

Przykład REPLACE()

Funkcja SQL REPLACE() zmienia lub podmienia wszystkie wystąpienia łańcucha na wskazany podłańcuch lub łańcuch. W poniższym przykładzie funkcja REPLACE() w SQL zastąpiła wartość old na new w kolumnie description. Zapytanie wykona się dla każdego wiersza, by zastąpić stary łańcuch nowym.

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

Uwaga: Bazy MySQL, PostgreSQL, Oracle i SQL Server obsługują funkcję REPLACE().

Zastosowania REPLACE() w SQL

Istnieje wiele ważnych zastosowań funkcji SQL REPLACE() w manipulacji tekstem i transformacji danych. Obejmują one m.in.:

Podstawowa podmiana

Możesz użyć funkcji SQL REPLACE(), aby wykonać podstawową podmianę. Załóżmy, że mamy następującą tabelę w naszej bazie SQL Server:

Przykładowa tabela Products. Obraz: autor.

Za pomocą poniższego zapytania SQL możesz użyć funkcji REPLACE(), aby zmienić status old na new.

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

Powstała tabela będzie wyglądała jak ta poniżej.

Tabela zaktualizowana przy użyciu SQL REPLACE(). Obraz: autor.

Zastępowanie wielu podłańcuchów

Jeśli chcesz zastąpić wiele podłańcuchów, użyj łańcuchowo wielu funkcji REPLACE(). Tutaj aktualizujemy wartość old na outdated oraz new na 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;

Powstała tabela jest pokazana poniżej.

Tabela zaktualizowana przy użyciu wielu podłańcuchów. Obraz: autor.

Usuwanie znaków

Przekaż pusty łańcuch jako trzeci argument, aby całkowicie usunąć znaki. To najczystszy sposób na usuwanie znaków interpunkcyjnych, nadmiarowych spacji lub znaków formatowania z kolumny.

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

Na przykład '555-867-5309' staje się '5558675309'. Ta sama technika usuwa znaki specjalne, artefakty kodowania lub niechciane białe znaki z importowanych danych.

Obsługa wielkości liter

Wrażliwość na wielkość liter w REPLACE() zależy od bazy danych i sortowania. PostgreSQL traktuje ją domyślnie jako wrażliwą, więc REPLACE(col, 'Old', 'New') nie dopasuje 'old'. SQL Server i MySQL stosują sortowanie kolumny lub bazy—w większości domyślnych instalacji używa się sortowania niewrażliwego na wielkość, więc REPLACE() dopasuje niezależnie od wielkości liter.

Gdy chcesz zastąpić wartości w sposób niewrażliwy na wielkość, użyj funkcji UPPER() lub LOWER(), aby przekonwertować tekst przed podmianą. Ta technika zapewnia spójność wartości.

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

Tabela zaktualizowana przy użyciu funkcji REPLACE() i UPPER(). Obraz: autor.

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

Tabela zaktualizowana przy użyciu funkcji SQL REPLACE() i LOWER(). Obraz: autor.

Zaawansowane techniki

Te techniki łączą REPLACE() z innymi funkcjami SQL, aby obsługiwać podstawienia wykraczające poza proste znajdź-i-podmień.

Zastępowanie wartościami dynamicznymi

Funkcja REPLACE() może służyć do wykonywania dynamicznych podmian w połączeniu z innymi funkcjami SQL. Na przykład, używając funkcji REPLACE(), możesz użyć wartości z innej kolumny do zastąpienia podłańcucha. W poniższym przykładzie funkcja REPLACE() zastępuje każde „old” rzeczywistą wartością z 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;

Tabela zaktualizowana przy użyciu funkcji REPLACE() i wartości dynamicznych. Obraz: autor.

Użycie REPLACE() w instrukcjach UPDATE

Funkcja SQL REPLACE() działa w instrukcjach UPDATE, aby modyfikować wartości kolumn w jednym lub wielu wierszach. Poniższe zapytanie aktualizuje wartość old na outdated w kolumnie status.

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

W bardziej złożonych scenariuszach, takich jak aktualizacja tabeli przez złączenie jej z inną, zobacz nasz poradnik SQL UPDATE z JOIN.

Łączenie REPLACE() z innymi funkcjami tekstowymi

Jedną z zaawansowanych technik jest łączenie REPLACE() z innymi funkcjami tekstowymi SQL, takimi jak CHARINDEX() czy SUBSTRING(), podczas zastępowania wartości łańcuchowych.

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

Powstała tabela z powyższego zapytania jest pokazana poniżej.

Tabela zaktualizowana przy użyciu REPLACE() i innych funkcji tekstowych. Obraz: autor.

Zastępowanie NULL wartością 0

Możesz użyć funkcji COALESCE(), aby zastąpić wartość NULL wartością awaryjną przed wywołaniem REPLACE(), jak pokazano poniżej.

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

Pułapka: Jeśli którykolwiek argument przekazany do REPLACE() ma wartość NULL, funkcja zwraca NULL dla tego wiersza—nawet jeśli pozostałe argumenty są poprawne. Owiń potencjalnie puste kolumny w COALESCE() przed wywołaniem REPLACE(), a nie po.

Praktyczne zastosowania funkcji SQL REPLACE()

Oto scenariusze, w których REPLACE() zasługuje na miejsce w zapytaniach produkcyjnych.

• Czyszczenie danych: Funkcja REPLACE() jest przydatna do czyszczenia danych, usuwania i zastępowania niepożądanych znaków lub spacji w łańcuchach w zbiorze danych.

• Transformacja danych: Funkcja REPLACE() służy w transformacji danych do zmiany wartości na inne formaty i zastępowania przestarzałych wartości nowymi.

• Obsługa danych od użytkowników: Funkcja REPLACE() jest ważna przy dostosowywaniu i konwertowaniu danych wejściowych użytkowników do jednolitego standardu akceptowalnego w danej bazie. Ta technika pomaga zapewnić spójność w bazie danych.

• Standaryzacja tekstu: Funkcja REPLACE() standaryzuje wartości w kolumnach tekstowych, aby zapewnić jednolitość, np. skrótów w formatach małymi lub wielkimi literami.

• Warunkowa podmiana: Funkcja REPLACE() jest też przydatna w prostych praktykach zarządzania bazą, aby zastępować wartości zgodnie z określonymi regułami.

Implementacje REPLACE() specyficzne dla baz danych

Bazy MySQL, PostgreSQL, Oracle i SQL Server obsługują funkcję REPLACE() w SQL, a jej składnia w tych bazach jest podobna.

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

Wydajność REPLACE() i dobre praktyki

Korzystając z funkcji REPLACE(), warto pamiętać o następujących kwestiach wydajnościowych i dobrych praktykach.

Wpływ na wydajność zapytań

Funkcja REPLACE() może wpływać na wydajność zapytań, zwłaszcza przy dużych zbiorach danych. Dzieje się tak, ponieważ funkcja aktualizuje tabelę dla każdego wiersza i wykonanie zapytania może zająć dłuższą chwilę.

Kwestie indeksowania

Zindeksuj kolumnę, po której filtrujesz, zanim użyjesz REPLACE() w klauzuli WHERE lub UPDATE. Bez indeksu baza przeszukuje każdy wiersz w poszukiwaniu dopasowań—co jest szczególnie kosztowne przy dużych tabelach.

Monitorowanie i rozwiązywanie wąskich gardeł wydajności

Aby rozwiązywać problemy z wydajnością zapytań, weź pod uwagę następujące dobre praktyki:

• Używaj narzędzi do profilowania bazy, aby monitorować wydajność zapytań i optymalizować je przy dużych zbiorach danych.

• Gdy używasz REPLACE() do modyfikacji wartości tekstowych, regularnie utrzymuj indeksy, aby były zgodne.

• Stosuj przetwarzanie wsadowe, aby skrócić czas wykonania zapytań przy obsłudze dużych zbiorów danych.

Aby poznać inne zaawansowane funkcje SQL do transformacji danych, polecam kurs DataCamp Learn SQL. Kurs Reporting in SQL obejmuje również tematy dotyczące optymalizacji baz, pomagając poznać różne techniki poprawy wydajności.

Na zakończenie

Funkcja REPLACE() pokrywa większość potrzeb związanych z podstawianiem tekstu w bazach SQL. Pamiętaj o zachowaniu przy NULL—dowolny argument równy NULL zwraca NULL—oraz sprawdzaj ustawienia sortowania bazy, gdy liczy się wrażliwość na wielkość liter.

Możesz dowiedzieć się więcej o funkcji REPLACE() w SQL, przerabiając kursy DataCamp Intermediate SQL i SQL Fundamentals. Polecam też ścieżkę kariery Associate Data Analyst in SQL, aby poznać więcej zastosowań SQL w transformacji danych i przyspieszyć karierę. Na koniec zachęcam do zdobycia SQL Associate Certification w DataCamp, aby zaprezentować swoje osiągnięcia w używaniu SQL do analizy danych i wyróżnić się na rozmowach kwalifikacyjnych.

Jeśli chcesz poznać więcej funkcji SQL używanych w analizie danych, sprawdź też inne moje poradniki, w tym:

• 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