Postgres: convert list of values "name = value" to table
Convert this string:
n1=10;n2=50;n3=60;n4=20
using something like this (in Postgres 9.x):
select *
from (some_engine_to_convert_this('n1=10;n2=50;n3=60;n4=20')) t
to get a result like this:
Name Value
v1 10
v2 50
v3 60
v4 20
PS: I can't create any function, so I only need to use Postgres built-in functions.
Thanks in advance.
source to share
select split_part(nv, '=', 1) as name,
split_part(nv, '=', 2) as value
from (
select unnest(string_to_array('n1=10;n2=50;n3=60;n4=20',';'))
) as t (nv);
string_to_array
first creates an array of key / value pairs. unnest
turns this into strings, and the key / value elements are retrieved from the result using split_part
.
Alternatively, a combination of unnest
and string_to_array
can be combined with regexp_split_to_table
, but regex functions are usually slower. Not sure which one would be more effective.
SQLFiddle example: http://sqlfiddle.com/#!15/d41d8/2991
source to share
Alternative answer : taken from a_horse_with_no_name
with cte as(
select unnest(string_to_array('n1=10;n2=50;n3=60;n4=20',';'))
)
select split_part(unnest, '=', 1) as name,
split_part(unnest, '=', 2) as value
from cte
source to share