REPLACE SQL: Ganti Karakter Massal Saat Bersihin Data

REPLACE ganti semua kemunculan sebuah teks di dalam kolom dengan teks lain. Sintaksnya REPLACE(teks, yang_dicari, penggantinya).

Fungsi ini yang bakal kamu pakai tiap kali dapat kolom nomor HP yang formatnya beda-beda: ada yang pakai tanda hubung, ada yang pakai titik, ada yang diawali +62. Satu query, semuanya seragam.

Dan di database, data kotor bukan cuma bikin laporan jelek. Data kotor bikin JOIN gagal diam-diam.

Apa itu REPLACE di SQL?

REPLACE adalah fungsi string yang nyari sebuah substring di dalam teks, terus nukar semua kemunculannya dengan substring lain. Bukan cuma yang pertama. Semuanya sekaligus.

REPLACE(string_sumber, string_yang_dicari, string_pengganti)

Tiga bahan, semuanya wajib:

Argumen	Isi	Contoh
string_sumber	Kolom atau teks yang mau dibersihin	no_hp
string_yang_dicari	Karakter atau kata yang mau diganti	'-'
string_pengganti	Penggantinya. Kosongin buat hapus	''

Query paling dasarnya kayak gini:

SELECT REPLACE('0812-3456-7890', '-', '') AS hp_bersih;
-- hasil: 081234567890

Kabar bagusnya, REPLACE jalan di semua database besar dengan sintaks yang sama: PostgreSQL, MySQL, SQL Server, SQLite. Nggak perlu hafal versi berbeda.

Gimana cara pakai REPLACE buat bersihin nomor HP?

Ini kasus paling sering. Data nomor HP dari form online biasanya campur aduk formatnya.

SELECT nama,
       no_hp,
       REPLACE(no_hp, '-', '') AS hp_bersih
FROM pelanggan;

Masalahnya, tanda hubung bukan satu-satunya pengganggu. Ada spasi, titik, tanda kurung. Solusinya: tumpuk REPLACE-nya.

SELECT nama,
       REPLACE(REPLACE(REPLACE(
         no_hp, '-', ''), ' ', ''), '.', '') AS hp_bersih
FROM pelanggan;

Bacanya dari dalam ke luar. REPLACE paling dalam jalan duluan, hasilnya jadi input buat REPLACE di luarnya.

Kalau tumpukannya udah lewat 5 lapis, query-nya mulai susah dibaca. Di PostgreSQL kamu bisa pakai TRANSLATE yang lebih ringkas:

SELECT TRANSLATE(no_hp, '-. ()', '') AS hp_bersih
FROM pelanggan;

Satu baris, lima karakter kebuang sekaligus. Sayangnya TRANSLATE nggak ada di MySQL dan SQLite.

Gimana cara ganti awalan +62 jadi 0?

Nomor HP Indonesia punya dua format yang sama-sama valid: 08123456789 dan +628123456789. Kalau dua format ini nyampur di satu kolom, hitungan pelanggan unik kamu bakal double.

SELECT no_hp,
       REPLACE(REPLACE(no_hp, ' ', ''), '+62', '0') AS hp_lokal
FROM pelanggan;

Perhatiin urutannya. Spasi dihapus duluan, baru +62 diganti. Kalau kebalik, nomor kayak +62 813 9988 776 nggak bakal kena. Soalnya yang tertulis di data itu +62 diikuti spasi, dan REPLACE nyari kecocokan persis.

Urutan itu penting di REPLACE bersarang. Salah urutan = hasil setengah bersih.

Contoh kasus: kode produk toko_berkah

Dataset toko_berkah punya 3.400 baris transaksi. Kolom kode_produk diisi tiga orang berbeda selama dua tahun, dan hasilnya persis kayak yang kamu duga: PRD_001, PRD-001, prd 001.

Waktu aku hitung produk unik pakai COUNT(DISTINCT kode_produk), hasilnya 287. Setelah dinormalisasi, jumlahnya turun jadi 194.

93 kode itu duplikat. Artinya sepertiga katalog produk yang muncul di laporan sebenernya barang yang sama, cuma beda cara nulis.

SELECT UPPER(REPLACE(REPLACE(kode_produk, '_', '-'), ' ', '-')) AS kode_rapi,
       COUNT(*)      AS jumlah_transaksi,
       SUM(total)    AS omzet
FROM transaksi
GROUP BY 1
ORDER BY omzet DESC;

REPLACE nyeragamkan pemisahnya, UPPER nyeragamkan huruf besar-kecilnya. Dua fungsi, katalog produk langsung kebaca. Soal UPPER dan LOWER aku bahas terpisah di UPPER dan LOWER SQL.

Gimana cara pakai REPLACE di UPDATE dengan aman?

REPLACE di SELECT itu aman. Data aslinya nggak kesentuh. Tapi begitu kamu pakai di UPDATE, perubahannya permanen.

Urutan yang aku pakai selalu tiga langkah:

1. Hitung dulu berapa baris yang bakal kena.

   SELECT COUNT(*) AS baris_terdampak
   FROM pelanggan
   WHERE no_hp <> REPLACE(no_hp, '-', '');

   Kalau angkanya jauh dari ekspektasi, berhenti. Ada yang salah di logika kamu.
2. Lihat sampel hasilnya.

   SELECT no_hp AS sebelum,
          REPLACE(no_hp, '-', '') AS sesudah
   FROM pelanggan
   WHERE no_hp <> REPLACE(no_hp, '-', '')
   LIMIT 20;

   Mata kamu bakal nangkep yang query nggak nangkep.
3. Baru UPDATE, dibungkus transaksi.

   BEGIN;
   
   UPDATE pelanggan
   SET no_hp = REPLACE(no_hp, '-', '')
   WHERE no_hp <> REPLACE(no_hp, '-', '');
   
   -- cek hasilnya dulu
   SELECT no_hp FROM pelanggan LIMIT 20;
   
   COMMIT;   -- atau ROLLBACK kalau ada yang aneh

WHERE-nya penting. Tanpa itu, database bakal nulis ulang semua baris, termasuk yang udah bersih. Di tabel 3 juta baris, itu beda antara UPDATE 2 detik dan UPDATE 4 menit. Konsep transaksi ini aku bahas lebih dalam di transaksi.

Kesalahan umum waktu pakai REPLACE

• Lupa REPLACE itu peka huruf besar-kecil. REPLACE(kota, 'jakarta', 'Jakarta') nggak bakal kena JAKARTA. Normalkan dulu: REPLACE(LOWER(kota), 'jakarta', 'Jakarta').
• UPDATE tanpa WHERE. Semua baris kena tulis ulang. Lambat, dan riwayat perubahan jadi kotor.
• Ngira REPLACE cuma ganti yang pertama. REPLACE ganti SEMUA kemunculan. Kalau kamu cuma mau yang pertama, butuh kombinasi POSITION dan SUBSTRING.
• Lupa NULL nular. REPLACE(NULL, '-', '') hasilnya NULL, bukan string kosong. Bungkus pakai COALESCE kalau perlu.
• Pakai REPLACE buat pola yang rumit. Kalau yang mau kamu ganti itu pola (misalnya semua angka, atau semua karakter non-huruf), REPLACE bakal jadi tumpukan 15 lapis. Pakai REGEXP_REPLACE.

Kapan pakai REGEXP_REPLACE?

REPLACE nyari teks persis. REGEXP_REPLACE nyari pola. Kalau kamu mau hapus semua karakter yang bukan angka dari nomor HP, satu baris cukup:

-- PostgreSQL
SELECT REGEXP_REPLACE(no_hp, '[^0-9]', '', 'g') AS hp_bersih
FROM pelanggan;

Tanda g di ujung artinya global. Ganti semua yang cocok, bukan cuma yang pertama. Detail sintaksnya bisa kamu cek di dokumentasi fungsi string PostgreSQL.

Aturan praktisnya: karakter yang bisa kamu sebut satu-satu, pakai REPLACE. Pola yang harus dijelasin, pakai REGEXP_REPLACE.

FAQ

Apa bedanya REPLACE dan TRANSLATE di SQL?

REPLACE ganti satu substring dengan substring lain, panjangnya boleh beda. TRANSLATE (PostgreSQL dan Oracle) ganti karakter satu per satu berdasarkan pemetaan. Buat hapus banyak tanda baca sekaligus, TRANSLATE lebih pendek. Di MySQL dan SQLite, TRANSLATE nggak ada. Tetap pakai REPLACE bersarang.

Apakah REPLACE di SQL case-sensitive?

Ya, di PostgreSQL selalu case-sensitive. REPLACE(kota, 'jakarta', 'Jakarta') nggak bakal kena JAKARTA. Cara aman: normalkan dulu pakai LOWER, baru REPLACE.

Gimana cara REPLACE lebih dari satu karakter sekaligus?

Tumpuk REPLACE-nya. Hasil yang di dalam jadi input yang di luar. Kalau tumpukannya udah lewat 5 lapis, mending pakai TRANSLATE atau REGEXP_REPLACE biar query-nya masih kebaca.

REPLACE mengubah data aslinya nggak?

Nggak, kalau dipakai di SELECT. Data baru berubah permanen kalau REPLACE dipakai di dalam UPDATE. Biasakan SELECT dulu buat lihat hasilnya, dan bungkus UPDATE besar dalam transaksi biar bisa ROLLBACK.

Apa yang terjadi kalau kolomnya NULL?

Hasilnya NULL, bukan string kosong. Hampir semua fungsi string di SQL nularin NULL. Bungkus pakai COALESCE kalau kamu mau nilai default.

Penutup

Yang perlu nempel di kepala:

• REPLACE ganti SEMUA kemunculan, bukan cuma yang pertama.
• Tumpuk buat banyak karakter, tapi perhatiin urutannya.
• Sebelum UPDATE: hitung, lihat sampel, baru jalankan dalam transaksi.

Pembersihan data itu 60% pekerjaan analis, dan REPLACE salah satu alat yang paling sering kepakai. Sisanya biasanya soal spasi, yang aku bahas di TRIM SQL.

Latihan query REPLACE langsung di browser tanpa install apa-apa? Coba modul fungsi string di NgulikSQL.