Regex di SQL: Pola Teks buat Bersihin Data Kotor

Regex di SQL adalah cara nyari dan ngubah teks berdasarkan pola, bukan berdasarkan kata yang persis sama.

Kalau kamu pernah export data pelanggan dan nemu kolom nomor HP yang isinya 081234567890, +6281234567891, 0812-3456-7892, dan tidak ada, kamu udah butuh regex.

LIKE gak sanggup nangani itu. Regex sanggup.

Di tutorial ini kamu bakal dapat pola-pola siap pakai buat bersihin nomor HP, email, dan alamat dari data Indonesia yang berantakan.

Apa itu regex di SQL?

Regex, kependekan dari regular expression, adalah bahasa kecil buat mendeskripsikan pola teks. Di SQL, regex dipakai buat tiga hal: nyari baris yang cocok pola, validasi format data, dan ganti karakter berdasarkan aturan.

Contoh polanya: ^08[0-9]{9,11}$. Artinya, teks yang mulai dengan 08, terus diikuti 9 sampai 11 digit angka, dan berakhir di situ.

Empat simbol yang paling sering kepake:

Simbol	Artinya	Contoh
^	awal teks	^Jl: mulai dengan "Jl"
$	akhir teks	com$: berakhir dengan "com"
[0-9]	satu digit angka	[0-9][0-9]: dua digit
{n,m}	diulang n sampai m kali	[0-9]{9,11}: 9 sampai 11 digit

Sisanya bisa kamu pelajari sambil jalan. Empat ini udah nyelesaiin 80 persen kasus pembersihan data.

Apa bedanya regex sama LIKE?

LIKE cuma paham dua wildcard: % buat karakter apa aja, dan _ buat satu karakter.

Jadi WHERE nama LIKE 'B%' jalan buat nyari nama yang mulai huruf B. Tapi coba minta LIKE nyari "nomor HP yang tepat 12 digit angka". Gak bisa.

Regex bisa, soalnya regex ngerti konsep "digit" dan "berapa kali diulang".

Kalau pencarianmu sesederhana "mulai dengan" atau "mengandung", pakai LIKE aja. Lebih cepat dan lebih gampang dibaca. Detail perbandingannya ada di halaman LIKE.

Gimana sintaks regex di PostgreSQL dan MySQL?

Ini bagian yang bikin orang bingung. Fungsinya beda tergantung database.

Kebutuhan	PostgreSQL	MySQL 8
Cocok pola	kolom ~ 'pola'	REGEXP_LIKE(kolom, 'pola')
Gak cocok pola	kolom !~ 'pola'	NOT REGEXP_LIKE(kolom, 'pola')
Abaikan huruf besar-kecil	kolom ~* 'pola'	REGEXP_LIKE(kolom, 'pola', 'i')
Ganti teks	REGEXP_REPLACE(kolom, 'pola', 'ganti', 'g')	REGEXP_REPLACE(kolom, 'pola', 'ganti')
Ambil bagian yang cocok	REGEXP_MATCHES(...)	REGEXP_SUBSTR(...)

Perhatiin flag 'g' di PostgreSQL. Tanpa itu, REGEXP_REPLACE cuma ganti kecocokan pertama. Ini jebakan yang bikin aku pernah kehilangan 20 menit.

SQLite gak punya regex bawaan. Kalau kamu latihan di SQLite, harus load extension dulu.

Gimana cara bersihin nomor HP pakai regex?

Ini kasus paling sering di data Indonesia. Satu nomor bisa ditulis lima cara berbeda.

Langkah 1: cari yang bermasalah dulu

SELECT nama, no_hp
FROM pelanggan
WHERE no_hp !~ '^08[0-9]{9,11}$';

Query ini nunjukin baris mana aja yang formatnya gak standar. Lihat dulu sebelum benerin.

Langkah 2: buang semua karakter selain angka

SELECT nama,
       no_hp AS asli,
       REGEXP_REPLACE(no_hp, '[^0-9]', '', 'g') AS bersih
FROM pelanggan;

Pola [^0-9] artinya "apa pun yang bukan digit". Tanda ^ di dalam kurung siku artinya negasi. Beda sama ^ di luar kurung yang artinya awal teks.

Hasilnya: 0812-3456-7892 jadi 081234567892. Strip hilang.

Langkah 3: normalisasi prefix 62 jadi 08

SELECT nama,
       REGEXP_REPLACE(
         REGEXP_REPLACE(no_hp, '[^0-9]', '', 'g'),
         '^62', '0'
       ) AS no_hp_standar
FROM pelanggan;

Dua REGEXP_REPLACE ditumpuk. Yang dalam buang karakter aneh, yang luar ganti awalan 62 jadi 0.

Sekarang +6281234567891 jadi 081234567891. Satu format buat semua.

Gimana cara validasi email pakai regex?

Pola email yang cukup buat kerja sehari-hari:

SELECT nama, email,
       CASE WHEN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
            THEN 'valid' ELSE 'perlu dicek' END AS status_email
FROM pelanggan;

Dibaca dari kiri: satu atau lebih karakter yang boleh, terus @, terus nama domain, terus titik, terus minimal dua huruf.

Pola ini nolak budi@@gmail.com (dua @), dedi[at]gmail.com (gak ada @ sama sekali), dan maya@gmail (gak ada titik domain).

Regex email yang bener-bener sesuai standar RFC panjangnya ratusan karakter dan gak ada yang pakai. Yang di atas udah cukup buat nyaring 99 persen kesalahan input.

Gimana cara rapikan alamat yang spasinya berantakan?

SELECT nama,
       TRIM(REGEXP_REPLACE(alamat, '\s+', ' ', 'g')) AS alamat_rapi
FROM pelanggan;

Pola \s+ artinya "satu atau lebih karakter spasi" (termasuk tab dan enter). Diganti jadi satu spasi tunggal.

Terus TRIM buang spasi di ujung. Hasilnya: Jl. Merdeka No.15 jadi Jl. Merdeka No.15.

Mau ambil nomor rumahnya doang? Pakai grup tangkapan:

SELECT nama,
       (REGEXP_MATCHES(alamat, 'No\.?\s*([0-9]+)', 'i'))[1] AS nomor_rumah
FROM pelanggan;

Kurung buka-tutup di ([0-9]+) nandain bagian yang mau diambil. Flag 'i' bikin "No", "no", dan "NO" sama-sama kena.

Contoh kasus: bersihin kontak pelanggan toko_berkah

Aku jalanin pola-pola di atas ke tabel kontak pelanggan toko_berkah, 1.240 baris hasil kumpulan dari kasir, form Google, dan catatan WhatsApp.

Temuan	Jumlah baris	Persen
Nomor HP format non-standar	389	31,4%
Pakai prefix +62 atau 62	176	14,2%
Ada strip atau spasi di nomor	151	12,2%
Email gak lolos validasi	94	7,6%
Nomor HP isinya teks ("tidak ada", "-")	62	5,0%

Yang menarik: sebelum dibersihin, jumlah pelanggan unik kehitung 1.240. Setelah nomor HP dinormalisasi jadi satu format, ketahuan 213 di antaranya orang yang sama: daftar dua kali dengan format nomor beda.

Jumlah pelanggan asli cuma 1.027. Angka pelanggan di laporan sebelumnya kelebihan 20,7 persen.

Satu query regex, satu kesimpulan bisnis yang berubah.

Apa kesalahan umum waktu pakai regex di SQL?

Lupa flag 'g' di PostgreSQL. REGEXP_REPLACE tanpa 'g' cuma ganti kecocokan pertama. Nomor 0812-3456-7892 jadi 08123456-7892. Setengah bersih itu lebih bahaya dari gak bersih sama sekali.

Langsung UPDATE tanpa SELECT dulu. Jalanin SELECT, lihat kolom asli dan hasil berdampingan, baru UPDATE. Regex salah yang udah nge-UPDATE 5.000 baris gak bisa di-undo.

Lupa jangkar ^ dan $. Pola [0-9]{11} tanpa jangkar bakal cocok sama teks apa pun yang mengandung 11 digit berurutan, termasuk abc08123456789xyz. Kasih jangkar biar dia cek seluruh teks.

Regex di kolom yang di-index. Regex biasanya bikin database ngabaikan index dan scan semua baris. Di tabel besar, saring dulu pakai kondisi murah, baru regex.

Kelupaan NULL. Regex sama NULL hasilnya NULL, bukan false. Baris NULL gak bakal kena WHERE apa pun. Bungkus pakai COALESCE kalau mau NULL ikut dicek.

FAQ

Regex di SQL itu apa?

Regex atau regular expression adalah cara nulis pola teks. Di SQL, regex dipakai buat nyari, validasi, dan ganti teks yang bentuknya gak persis sama. Contohnya nyari semua nomor HP yang mulai dari 08 dan panjangnya 11 sampai 13 digit. LIKE gak bisa ngelakuin itu soalnya LIKE gak paham konsep digit atau pengulangan.

Bedanya REGEXP sama LIKE apa?

LIKE cuma punya dua wildcard: persen dan underscore. Regex punya puluhan simbol: kelas karakter, jumlah pengulangan, jangkar awal-akhir, dan grup tangkapan. LIKE cocok buat pencarian sederhana. Regex cocok buat validasi format dan pembersihan pola yang rumit.

Regex SQL sama di semua database?

Sintaksnya beda. PostgreSQL pakai operator tilde dan REGEXP_REPLACE dengan flag g. MySQL 8 pakai REGEXP_LIKE dan REGEXP_SUBSTR. SQLite gak punya regex bawaan. Polanya sendiri mirip soalnya kebanyakan ngikutin standar POSIX, jadi ilmu regex kamu tetap kepake pindah database.

Regex bikin query jadi lambat gak?

Iya, regex lebih berat dari perbandingan biasa dan biasanya gak bisa pakai index. Di tabel 4.800 baris selisihnya gak berasa. Di tabel 5 juta baris bisa jadi masalah. Trik yang aku pakai: saring dulu pakai kondisi murah, baru regex jalan di sisa barisnya.

Gimana cara nyoba regex sebelum dipakai di query beneran?

Jalanin SELECT dulu tanpa UPDATE. Tampilkan kolom asli dan kolom hasil regex berdampingan, terus bandingkan. Kalau udah yakin baru diubah jadi UPDATE. Jangan pernah UPDATE pakai regex yang belum pernah diuji.

Langkah berikutnya

Regex kelihatan serem waktu pertama lihat. Tapi empat simbol (^, $, [0-9], {n,m}) udah bisa nyelesaiin kebanyakan pekerjaan pembersihan data.

Mulai dari nomor HP. Itu kasus paling sering dan paling gampang kelihatan hasilnya.

Sintaks lengkap regex PostgreSQL ada di dokumentasi resmi PostgreSQL. Buat langkah pembersihan yang lain, cek Checklist Data Cleaning 12 Langkah.

Mau latihan langsung tanpa install database? Coba editor interaktif di NgulikSQL, tinggal ketik query dan langsung lihat hasilnya.