String

REGEXP_EXTRACT

BigQuery

Mengekstrak substring pertama yang cocok dengan regular expression. Jika pattern memiliki capturing group, mengembalikan grup tersebut.

Tipe hasil: STRINGDiperbarui: 7 Jan 2026

Syntax

SQL

REGEXP_EXTRACT(value, regexp [, position [, occurrence]])

valueSTRINGwajib

String sumber

regexpSTRINGwajib

Regular expression dengan optional capturing group

positionINT64opsional

Posisi awal pencarian (1-based)

Default: 1

occurrenceINT64opsional

Occurrence ke-n yang akan diekstrak

Default: 1

SQL

1 SELECT
2   email,
3   REGEXP_EXTRACT(email, r'@([a-zA-Z0-9.-]+)') as domain
4 FROM `project.dataset.users`;

Ekstrak domain dari email menggunakan capturing group.

Hasil

email	domain
john@gmail.com	gmail.com
jane@company.co.id	company.co.id

SQL

1 SELECT
2   text,
3   REGEXP_EXTRACT(text, r'[0-9]+') as first_number
4 FROM `project.dataset.logs`;

Ekstrak angka pertama dari text.

Hasil

text	first_number
Order #12345 confirmed	12345
Invoice 2024-001	2024

SQL

1 SELECT
2   REGEXP_EXTRACT('abc123def456', r'[0-9]+', 1, 2) as second_number;

Ekstrak occurrence kedua dari pattern.

Hasil

456

REGEXP_MATCH

PostgreSQL

REGEXP_SUBSTR

MySQL

REGEXP_SUBSTR

Snowflake

REGEXP_EXTRACT

DuckDB

1	SELECT
2	email,
3	REGEXP_EXTRACT(email, r'@([a-zA-Z0-9.-]+)') as domain
4	FROM `project.dataset.users`;

1	SELECT
2	text,
3	REGEXP_EXTRACT(text, r'[0-9]+') as first_number
4	FROM `project.dataset.logs`;

1	SELECT
2	REGEXP_EXTRACT('abc123def456', r'[0-9]+', 1, 2) as second_number;