How to separate values from string into columns in postgresql?

Question

I have the following table:

tlb (Text)               value(Integer)   cost (numeric)
150;itema;section;shelf      5                0.5
120;itemb;shelf;box          5                2.3
151;itemf;section;shelf      5                1.5

I want to convert this table into this table:

a        b         c        d   value   cost
150    itema    section    shelf   5    0.5
120    itemb    shelf      box     5    2.3
151    itemf    section    shelf   5    1.5

basically break the tlb column to 4 different columns .

The structure of tlb is always the same string;string;string;string

How can i do that?

you can use split_part eg

Vao Tsun
– Vao Tsun

2017-03-22 12:06:18 +00:00
Commented Mar 22, 2017 at 12:06 — Vao Tsun
– Vao Tsun, Commented Mar 22, 2017 at 12:06

Vao Tsun · Accepted Answer · 2017-03-22 12:07:22Z

1

you can use split_part, Eg:

t=# with s as (select '150;itema;section;shelf'::text d)
select
  split_part(d,';',1) a
, split_part(d,';',2) b
, split_part(d,';',3) c
, split_part(d,';',4) d
from s;
  a  |   b   |    c    |   d
-----+-------+---------+-------
 150 | itema | section | shelf
(1 row)

answered Mar 22, 2017 at 12:07

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Piotr Rogowski · Accepted Answer · 2017-03-22 12:07:11Z

1

show this:

select
exe2.arr[1] as a,
exe2.arr[2] as b,
exe2.arr[3] as c,
exe2.arr[4] as d
from
(
    select
    exe.x,
    string_to_array(exe.x,';') as arr
    from
    (
        select
        '150;itema;section;shelf' as x
        union select
        '120;itemb;shelf;box' as x
        union select
        '151;itemf;section;shelf' as x
    ) exe
) exe2

answered Mar 22, 2017 at 12:07

Piotr Rogowski

3,90021 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to separate values from string into columns in postgresql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related