Cara Menggunakan Fungsi SQL REPLACE()

TL;DR

• REPLACE(string, old_substring, new_substring) menukar setiap kemunculan substring dalam satu pemanggilan

• Rangkaikan pemanggilan untuk mengganti banyak substring: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Gunakan string kosong untuk menghapus karakter: REPLACE(phone, '-', '')

• Jika ada argumen yang NULL, fungsi mengembalikan NULL untuk baris tersebut

• Kepekaan huruf besar/kecil bergantung pada database dan kolasi Anda—SQL Server dan MySQL secara bawaan tidak peka huruf; PostgreSQL secara bawaan peka huruf

Memahami Fungsi SQL REPLACE()

Fungsi REPLACE() dalam SQL mencari substring atau string yang ditentukan pada sebuah kolom dan menggantinya dengan string lain yang diberikan. Contoh di bawah menunjukkan penggunaan dasar fungsi REPLACE(). Argumen dalam fungsi meliputi:

• string: String asli tempat Anda akan melakukan penggantian.

• old_substring: Substring yang akan diganti.

• new_substring: Substring pengganti untuk menggantikan yang lama.

REPLACE(string, old_substring, new_substring)

Saat menggunakan fungsi REPLACE() untuk mengubah string di sebuah tabel, kueri akan memiliki sintaks berikut, di mana:

• column_name: Kolom yang digunakan untuk mencari kemunculan substring.

• old_substring: Substring yang akan diganti.

• new_substring: Substring pengganti untuk menggantikan yang lama.

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

Contoh REPLACE()

Fungsi SQL REPLACE() mengubah atau mengganti semua kemunculan string dengan substring atau string yang ditentukan. Pada contoh di bawah, fungsi REPLACE() dalam SQL telah mengganti nilai old dengan new pada kolom description. Kueri akan mengeksekusi setiap baris untuk mengganti string lama dengan yang baru.

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

Catatan: Database MySQL, PostgreSQL, Oracle, dan SQL Server semuanya mendukung fungsi REPLACE().

Use Case REPLACE() di SQL

Ada banyak use case penting dari fungsi SQL REPLACE() dalam manipulasi teks dan transformasi data. Di antaranya:

Penggantian dasar

Anda dapat menggunakan fungsi SQL REPLACE() untuk melakukan penggantian dasar. Misalkan kita memiliki tabel berikut di database SQL Server kita:

Contoh tabel Products. Gambar oleh Penulis.

Dengan kueri SQL berikut, Anda dapat menggunakan fungsi REPLACE() untuk mengubah status old menjadi new.

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

Tabel hasilnya akan terlihat seperti yang ditunjukkan di bawah.

Tabel diperbarui dengan SQL REPLACE(). Gambar oleh Penulis.

Mengganti beberapa substring

Jika Anda ingin mengganti beberapa substring, gunakan fungsi REPLACE() berantai. Di sini, kita memperbarui nilai old menjadi outdated, dan new menjadi 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;

Tabel hasilnya ditunjukkan di bawah.

Tabel diperbarui menggunakan beberapa substring. Gambar oleh Penulis.

Menghapus karakter

Masukkan string kosong sebagai argumen ketiga untuk menghapus karakter sepenuhnya. Ini adalah cara paling bersih untuk menghapus tanda baca, spasi berlebih, atau karakter pemformatan dari sebuah kolom.

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

Sebagai contoh, '555-867-5309' menjadi '5558675309'. Teknik yang sama dapat menghapus karakter khusus, artefak encoding, atau spasi yang tidak diinginkan dari data impor.

Menangani kepekaan huruf besar/kecil

Kepekaan huruf besar/kecil pada REPLACE() bergantung pada database dan kolasi Anda. PostgreSQL secara bawaan memperlakukannya peka huruf, sehingga REPLACE(col, 'Old', 'New') tidak akan cocok dengan 'old'. SQL Server dan MySQL mengikuti kolasi kolom atau database—sebagian besar instalasi default menggunakan kolasi yang tidak peka huruf, sehingga REPLACE() akan cocok terlepas dari huruf besar/kecil.

Saat Anda ingin mengganti nilai tanpa memperhatikan huruf besar/kecil, gunakan fungsi UPPER() atau LOWER() untuk mengonversi teks sebelum penggantian. Teknik ini menjaga konsistensi nilai.

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

Tabel diperbarui menggunakan fungsi REPLACE() dan UPPER(). Gambar oleh Penulis.

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

Tabel diperbarui menggunakan fungsi SQL REPLACE() dan LOWER(). Gambar oleh Penulis.

Teknik Lanjutan

Teknik-teknik ini menggabungkan REPLACE() dengan fungsi SQL lain untuk menangani penggantian yang melampaui sekadar temukan-dan-ganti.

Mengganti dengan nilai dinamis

Fungsi REPLACE() dapat digunakan untuk melakukan penggantian dinamis dengan mengombinasikannya dengan fungsi SQL lain. Misalnya, menggunakan fungsi REPLACE(), Anda dapat memakai nilai dari kolom lain untuk mengganti substring. Pada contoh di bawah, fungsi REPLACE() mengganti setiap produk lama dengan nilai aktual dari 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;

Tabel diperbarui menggunakan fungsi REPLACE() dan nilai dinamis. Gambar oleh Penulis.

Menggunakan REPLACE() dalam pernyataan UPDATE

Fungsi SQL REPLACE() dapat digunakan di dalam pernyataan UPDATE untuk memodifikasi nilai kolom di satu atau lebih baris. Kueri di bawah memperbarui nilai old menjadi outdated pada kolom status.

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

Untuk skenario yang lebih kompleks, seperti memperbarui tabel dengan menggabungkannya dengan tabel lain, lihat tutorial kami tentang SQL UPDATE with JOIN.

Menggabungkan REPLACE() dengan fungsi string lainnya

Salah satu teknik lanjutan adalah menggabungkan REPLACE() dengan fungsi string SQL lainnya, seperti CHARINDEX() atau SUBSTRING(), saat mengganti nilai string.

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

Tabel hasil dari kueri di atas ditunjukkan di bawah.

Tabel diperbarui menggunakan REPLACE() dan fungsi string lainnya. Gambar oleh Penulis.

Mengganti NULL dengan 0

Anda dapat menggunakan fungsi COALESCE() untuk mengganti nilai NULL dengan nilai fallback sebelum memanggil REPLACE(), seperti ditunjukkan di bawah.

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

Jebakan: Jika ada argumen yang diberikan ke REPLACE() bernilai NULL, fungsi akan mengembalikan NULL untuk baris tersebut—meskipun argumen lainnya valid. Bungkus kolom yang bisa bernilai null dengan COALESCE() sebelum memanggil REPLACE(), bukan setelahnya.

Aplikasi Praktis Fungsi SQL REPLACE()

Berikut adalah skenario di mana REPLACE() layak digunakan dalam kueri produksi.

• Pembersihan data: Fungsi REPLACE() berguna untuk pembersihan data, menghapus, dan mengganti karakter atau spasi yang tidak diinginkan dalam string di dalam sebuah dataset.

• Transformasi data: Fungsi REPLACE() digunakan dalam transformasi data untuk mengubah nilai ke format berbeda dan mengganti nilai usang dengan yang baru.

• Menangani input pengguna: Fungsi REPLACE() penting untuk menyesuaikan dan mengonversi data masukan pengguna ke standar seragam yang dapat diterima di database tertentu. Teknik ini membantu memastikan keseragaman dalam database.

• Standarisasi teks: Fungsi REPLACE() menstandarkan nilai di kolom string untuk memastikan keseragaman, seperti singkatan dalam format huruf kecil atau huruf besar.

• Penggantian kondisional: Fungsi REPLACE() juga berguna untuk praktik manajemen database yang mudah guna mengganti nilai sesuai aturan tertentu.

Implementasi Khusus Database untuk REPLACE()

Database MySQL, PostgreSQL, Oracle, dan SQL Server semuanya mendukung fungsi REPLACE() dalam SQL, dengan sintaks yang serupa di database tersebut.

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

Pertimbangan Kinerja dan Praktik Terbaik REPLACE()

Saat menggunakan fungsi REPLACE(), penting untuk memperhatikan pertimbangan kinerja dan praktik terbaik berikut.

Dampak pada kinerja kueri

Fungsi REPLACE() dapat memengaruhi kinerja kueri, terutama saat menangani dataset besar. Masalah ini terjadi karena fungsi memperbarui tabel untuk setiap baris dan mungkin membutuhkan waktu lama untuk mengeksekusi kueri.

Pertimbangan pengindeksan

Indekskan kolom yang Anda jadikan acuan kueri sebelum menjalankan REPLACE() dalam WHERE atau UPDATE. Tanpa indeks, database memindai setiap baris untuk mencari kecocokan—sangat mahal pada tabel besar.

Memantau dan mengatasi bottleneck kinerja

Untuk mengatasi masalah kinerja kueri, pertimbangkan praktik terbaik berikut:

• Gunakan alat profiling database untuk memantau kinerja kueri dan mengoptimalkan saat menangani dataset besar.

• Saat menggunakan REPLACE() untuk memodifikasi nilai string, lakukan pemeliharaan indeks secara berkala agar tetap selaras.

• Inisiasi pemrosesan batch untuk mengurangi waktu eksekusi kueri saat menangani dataset besar.

Untuk mempelajari fungsi SQL lanjutan lainnya untuk transformasi data, saya merekomendasikan kursus DataCamp Learn SQL. Kursus Reporting in SQL juga membahas topik tentang optimasi database, membantu Anda mempelajari berbagai teknik untuk mengoptimalkan kinerja.

Penutup

Fungsi REPLACE() mencakup sebagian besar kebutuhan penggantian teks di berbagai database SQL. Perhatikan perilaku NULL—argumen NULL apa pun mengembalikan NULL—dan verifikasi pengaturan kolasi database Anda saat kepekaan huruf besar/kecil penting.

Anda dapat mempelajari lebih lanjut tentang fungsi REPLACE() dalam SQL dengan mengikuti kursus DataCamp Intermediate SQL dan SQL Fundamentals. Saya juga merekomendasikan jalur karier Associate Data Analyst in SQL untuk mempelajari lebih lanjut tentang penerapan SQL dalam transformasi data dan memulai karier Anda. Terakhir, saya mendorong Anda mengikuti SQL Associate Certification dari DataCamp untuk membantu menampilkan pencapaian profesional Anda dalam menggunakan SQL untuk analisis data dan menonjol saat wawancara kerja.

Terakhir, jika Anda tertarik mempelajari lebih lanjut berbagai fungsi SQL yang digunakan dalam analisis data, saya sarankan melihat beberapa tutorial saya lainnya, termasuk:

• Cara Menggunakan Operator SQL BETWEEN
• Cara Menggunakan Klausa SQL LIMIT
• Cara Menggunakan Operator SQL IN
• Cara Menggunakan Klausa SQL OFFSET