String

REGEXP_MATCHES

PostgreSQL

Mencari semua matches pattern regex dan mengembalikan set of arrays. Gunakan flag g untuk global matching. Beda dengan REGEXP_MATCH yang hanya return first match.

Tipe hasil: setof text[]

Syntax

SQL

REGEXP_MATCHES(string, pattern [, flags])

Parameter

stringtextwajib

String tempat mencari

patterntextwajib

Regular expression pattern

flagstextopsional

Flags: g=global (return all matches)

Contoh Penggunaan

Find All Numbers

SQL

1 SELECT REGEXP_MATCHES(a1b2c3d4, ([0-9]), g);

Mencari semua angka dalam string.

Hasil

regexp_matches
{1}
{2}
{3}
{4}

Extract All Emails

SQL

1 SELECT m[1] AS email
2 FROM REGEXP_MATCHES(
3   Contact john@a.com or jane@b.com,
4   ([a-z]+@[a-z]+\.[a-z]+),
5   g
6 ) AS m;

Mengekstrak semua email dari text.

Hasil

email
john@a.com
jane@b.com

Parse Key-Value Pairs

SQL

1 SELECT m[1] AS key, m[2] AS value
2 FROM REGEXP_MATCHES(
3   name=John;age=30;city=Jakarta,
4   ([a-z]+)=([^;]+),
5   g
6 ) AS m;

Parsing string format key=value.

Hasil

key	value
name	John
age	30
city	Jakarta

Fungsi Terkait

REGEXP_REPLACEREGEXP_MATCH

Kembali ke PostgreSQL Functions

1	SELECT m[1] AS email
2	FROM REGEXP_MATCHES(
3	Contact john@a.com or jane@b.com,
4	([a-z]+@[a-z]+\.[a-z]+),
5	g
6	) AS m;

1	SELECT m[1] AS key, m[2] AS value
2	FROM REGEXP_MATCHES(
3	name=John;age=30;city=Jakarta,
4	([a-z]+)=([^;]+),
5	g
6	) AS m;