Search code examples
postgresqlsplitpostgresql-9.3

PostgreSQL 9.3: Split one column into multiple

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
Solution 
  • 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)