PostgreSQL 9.3: Split given string

Question

I want to split the given comma separated string into columns.

I have a table with two columns:

Example:

create table t3
(
 cola varchar,
 colb varchar
);

Insertion:

insert into t3 values('AD1','2000-01-01to2000-02-01'),
 ('AD2','2000-03-01to2000-04-01'),
 ('AD3','2000-05-01to2000-06-01');

Now I want prepare two comma separated strings from the given above records to like this:

str1 varchar = 'AD1,AD2,AD3';
str2 varchar = '2000-01-01to2000-02-01,2000年03月01日to2000-04-01,2000年05月01日to2000-06-01';

Now I want to store the comma separated string and in second string there is to for separation into two dates, want to store into the temp table.

Like this:

Expected Output:

c1 c2 c3
--------------------------------- 
AD1 2000年01月01日 2000年02月01日
AD2 2000年03月01日 2000年04月01日
AD3 2000年05月01日 2000年06月01日

Answer 1

You can get the output you want using:

select cola as col1,
 cast(left(colb, 10) as date) as col2,
 cast(right(colb, 10) as date) as col2
from t3;

I have no idea why you would want to create intermediate comma-separated strings, which are not needed for the logic.

• MAK: Wow! That's really great. And yes you are right, I don't need to create intermediate comma-separated strings. That's my bad logic.

Answer 2

split_part() is typically simplest and fastest for this purpose:

SELECT cola AS c1
 , split_part(colb, 'to', 1)::date AS c2
 , split_part(colb, 'to', 2)::date AS c3
FROM t3;

This is more robust and even works if one or both dates in the string vary in length. The cast requires a valid date string either way.
Related:

• Split comma separated column data into additional columns