parse key:value pairs in SQL postgresql-CodePudding

I have a string that contains a semicolon separated list of key value pairs.

E.g ref:12345;code:ab etc.

I would like to split it into 'ab' as code,'241376' as ref,etc.

Any help really appreciated.

CodePudding user response：

A little bit messy but I hope self explanatory.

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;

Result:

ref_fld	code_fld
12345	ab
9876	yz
5678	cd

CodePudding user response：

You can use a combination of regexp_split_to_table and split_part.

Have a look at PostgreSQL docs.

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

Result:

name	value
ref	12345
code	ab
ref	5678
code	cd

db<>fiddle here

UPDATE

According to your comment if your desired result is:

xxx as code
xxx as ref

You can use:

SELECT
    CONCAT(split_part(pair, ':', 2), ' as ', split_part(pair, ':', 1)) RESULT
FROM (SELECT regexp_split_to_table(myText, ';') pair FROM t) t1

That returns:

result
12345 as ref
ab as code
5678 as ref
cd as code

db<>fiddle here