CODEWITHSUNDEEP
sqlpostgresql
Christian
Christian

Reputation: 7320

Postgres: convert a list of "name=value" values into a 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 need to use Postgres built in functions only.

Thanks in advance.

Upvotes: 4

Views: 2223

Answers (2)

Vivek S.
Vivek S.

Reputation: 21935

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

Upvotes: 1

user330315
user330315

Reputation: 

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 that into rows and the key/value elements are then extracted from the result using split_part.

Alternatively the combination of unnest and string_to_array can be combined using regexp_split_to_table but the regex functions are usually slower. Not sure which one would be more efficient here.

SQLFiddle example: http://sqlfiddle.com/#!15/d41d8/2991

Upvotes: 7

Related Questions