特定の文字列を抽出したい

Question

特定の文字列を抽出したい

以下のように１行のレコードがあります。

AAA: aaa1,BBB: bbb1,BBB: bbb2,CCC: ccc1

※"KEY: val" のような文字列が複数項目あり、デリミタはカンマです。

抽出ルールは、 『KEYが "BBB" の、 val の値を抽出する』です。

理想の結果

bbb1,bbb2

補足

実際は複数レコードあり、項目数や並び順は決まっていません。
※1レコードに複数行あるのではなく、項目数や順序が様々なレコードが複数あります。

例）KEYとなる "BBB" の位置は先頭にある場合

BBB: bbb1,AAA: aaa1,BBB: bbb2,CCC: ccc1

出力結果

bbb1,bbb2

例）最終項目に1つだけある場合

AAA: aaa1,CCC: ccc1,BBB: bbb1

出力結果

bbb1

どのように SELECT したら良いか知恵を貸していただけませんか。

Comments

[A.Ichi]

複数行の場合はどのように表示するのでしょうか？

[mson]

1レコードに複数行あるのではなく、項目数や順序が様々なレコードが複数あります。例）の出力結果を記載しました。

[A.Ichi]

質問が良くなかったですね、BBBを含んでいる行を選択して表示する場合、1行で繋げて出すのか、複数行で出すのかをお聞きしたかったです。

[mson]

複数レコードの結果は、各々のレコードで出力します。（1レコードとして集約しない）

Answer 1

適当に調べながらやってみたらできましたが、普段PostgreSQLは使わないので、最適かどうかはわかりません。

SELECT ARRAY_TO_STRING(
    ARRAY(
        SELECT SUBSTRING(COL FROM 6) FROM (
            SELECT regexp_split_to_table('AAA: aaa1,BBB: bbb1,BBB: bbb2,CCC: ccc1', ',') AS COL
        ) A
        WHERE COL LIKE 'BBB%'
    ), ','
);

Comments

[mson]

ありがとうございます。解決しました。PostgreSQL 9.1 のため、STRING_AGG を使用しようと思います。アイデアをありがとうございました。

Answer 2

一行にまとめて出力します。

create temp table table_a (fld1 text);
insert into table_a values
('AAA: aaa1,BBB: bbb1,BBB: bbb2,CCC: ccc1'),
('AAA: aaa1,CCC: ccc1,BBB: bbb1'),
('CCC: ccc1,BBB: bbb1,AAA: aaa3');

select array_to_string(
       array(select substr(f1,strpos(f1,': ')+2) f2 from (
 select regexp_split_to_table(fld1,',') f1 from table_a
) t1 where f1 like 'BBB:%'
),',')
;

複数行で出力します

select
( select array_to_string(array(select substr(f1,strpos(f1,': ')+2)
  from (select unnest(string_to_array(fld1,',')) f1) t1 where f1 like 'BBB:%'),',')
)
from table_a where fld1 like '%BBB:%';

 array_to_string
-----------------
 bbb1,bbb2
 bbb1
(2 rows)

Comments

[mson]

ありがとうございます。実現できました。最も早く回答いただいたため、ベストアンサーとさせていただきました。

[ttyp03]

え…。回答した時間なら私の方が…。別にいいんですけどね、別に。

[A.Ichi]

そうですよね。正しく表示されてます。

[mson]

確かに、本当ですね。すみません、次の機会がありましたらまた宜しくお願い致します。