解析键:SQL postgresql中的值对

Question

我有一个字符串，它包含一个分号分隔的键值对列表。

E。G ref:12345;code:ab等

我想把它分成'ab'作为代码，'241376'作为ref，等等。

任何帮助都非常感谢。

Answer 1

您可以使用regexp_split_to_table和split_part的组合。

查看PostgreSQL文档

CREATE TABLE t (myText text);
INSERT INTO t VALUES ('ref:12345;code:ab;ref:5678;code:cd');
SELECT
split_part(pair, ':', 1) as name,
split_part(pair, ':', 2) as value
FROM
(SELECT regexp_split_to_table(myText, ';') pair FROM t) t1

结果:

123455678cd

有点乱，但我希望自己能解释清楚。

with t as 
(
select (r + 1)/2 as r, 
split_part(txt, ':', 1) as k, 
split_part(txt, ':', 2) as v
from unnest(string_to_array('ref:12345;code:ab;ref:5678;code:cd;ref:9876;code:yz', ';'))
with ordinality as t(txt, r)
)
select
max(v) filter (where k = 'ref') as ref_fld,
max(v) filter (where k = 'code') as code_fld
from t group by r;

结果:

<表类>ref_fldcode_fldtbody><<tr>12345ab9876yz5678cd