String

REGEXP_REPLACE

BigQuery

Mengganti semua substring yang cocok dengan regular expression dengan string pengganti. Mendukung backreferences.

Tipe hasil: STRINGDiperbarui: 7 Jan 2026

Syntax

SQL

REGEXP_REPLACE(value, regexp, replacement)

valueSTRINGwajib

String sumber

regexpSTRINGwajib

Regular expression pattern

replacementSTRINGwajib

String pengganti (support \1, \2 backreferences)

SQL

1 SELECT
2   phone,
3   REGEXP_REPLACE(phone, r'[^0-9]', '') as clean_phone
4 FROM `project.dataset.contacts`;

Hapus semua karakter non-numerik.

Hasil

phone	clean_phone
+62-812-345-678	62812345678
(021) 123 4567	0211234567

SQL

1 SELECT
2   REGEXP_REPLACE('20240115', r'(\d{4})(\d{2})(\d{2})', r'\1-\2-\3') as formatted;

Format date string dengan capturing groups.

Hasil

2024-01-15

SQL

1 SELECT
2   email,
3   REGEXP_REPLACE(email, r'(.).*@', r'\1***@') as masked
4 FROM `project.dataset.users`;

Mask bagian username dari email.

Hasil

email	masked
john@gmail.com	j***@gmail.com

1	SELECT
2	phone,
3	REGEXP_REPLACE(phone, r'[^0-9]', '') as clean_phone
4	FROM `project.dataset.contacts`;

1	SELECT
2	REGEXP_REPLACE('20240115', r'(\d{4})(\d{2})(\d{2})', r'\1-\2-\3') as formatted;

1	SELECT
2	email,
3	REGEXP_REPLACE(email, r'(.).@', r'\1**@') as masked
4	FROM `project.dataset.users`;