PostgreSQL で正規表現に最初にマッチしたテキストを返す

PostgreSQL Ver.10以上の場合

regexp_match() という関数を使うのが簡単です。

SELECT
  regexp_match('hogefuga','hoge');
-- {hoge}

戻りはTEXT配列です。
捕獲式集合が複数あった場合、捕捉グループが配列に収まります。

SELECT
  regexp_match('hogefuga','(hoge)(fuga)');
-- {hoge,fuga}

マッチしなければNULLが返ってきます。

SELECT
  regexp_match('hogefuga','piyo');
-- NULL

test_textsテーブルにこのようなデータがあったとして。

id	text
1	hoge
2	fuga
3	piyo
4	hogera
5	hogehoge
6	fugahoge

各行がhogeとfugaのどちらが先にtextに現れるかを調べたい。
かつ、どちらも含んでいなかった行も確認したい場合。

SELECT
  id
  ,(regexp_match(text,'(hoge|fuga)'))[1]
FROM
  test_texts
;

とすると、以下の結果が返ってくる。

id	text
1	hoge
2	fuga
3	[NULL]
4	hoge
5	hoge
6	fuga

PostgreSQLのドキュメントで添字に関して

一般的には、配列expressionは括弧で括らなければなりませんが、添字を付けるそのexpressionが単なる列参照や位置パラメータであった場合、その括弧を省略することができます。

と説明されているので、regexp_matchが返した配列に添字アクセスするためにregexp_match(text,'(hoge|fuga)')をカッコで囲んでいます。

PostgreSQL Ver.10未満の場合

regexp_match() が存在しません。バージョンをあげてください。
代わりにregexp_matches()を使います。

複数形の名称を持つこの関数はgフラグを与えると、マッチングするパターンが複数存在した場合に全てのパターンを複数行で返します。

SELECT
  regexp_matches('hoge1hoge2','hoge\d');
-- {hoge1}

SELECT
  regexp_matches('hoge1hoge2','hoge\d','g');
-- {hoge1}
-- {hoge2}

という訳でgフラグを立てない以下のようなSQLで同じようなことができます。

SELECT
  id
  ,(regexp_matches(text,'(hoge|fuga)'))[1]
FROM
  test_texts
;

ただし、regexp_matches()はマッチするものがない場合行を返しません。

id	text
1	hoge
2	fuga
4	hoge
5	hoge
6	fuga

どうしてもマッチしなかった行も回収したい場合は公式ドキュメントのヒントに従い、
SELECTをサブクエリにしてそこでregexp_matches()を呼びましょう。

SELECT
  id
  ,(SELECT (regexp_matches(text,'(hoge|fuga)'))[1])
FROM
  test_texts
;

これでバージョンの古いPostgreSQLでもregexp_match()と同じことができます。