PostgreSQL 9.3: Split one column into multiple

Question

I want to split one column that is colb in the given below example into two columns like column1 and column2.

I have a table with two columns:

Example:

create table t3
(
    cola varchar,
    colb varchar
);

Insertion:

insert into t3 values('D1','2021to123'),
                     ('D2','112to24201'),
                     ('D3','51to201');

I want to split the colb values into two columns like the following expected result:

Expected Result:

cola      column1        column2
---------------------------------
D1        2021           123
D2        112            24201
D3        51             201

Need some more clarification,you want to get 3 columns in selct or creating additional 2 column before insert into t3 ? — Vivek S., Mar 12 '15 at 05:03
@unique_id, Yes! It is. I want 3 columns in the `select` statement. — MAK, Mar 12 '15 at 05:04

score 17 · Accepted Answer · edited Jun 20 '20 at 09:12

17

select cola
      ,split_part(colb, 'to', 1) col1
      ,split_part(colb, 'to', 2) col2 
from t3

Quoted from the PostgreSQL Documentation:

split_part(string text, delimiter text, field int)

Split string on delimiter and return the given field (counting from one)

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 12 '15 at 05:10

Vivek S.

19,945
7
68
85

PostgreSQL 9.3: Split one column into multiple

1 Answers1

Linked