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-01to2000-04-01,2000-05-01to2000-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

Gordon Linoff · Accepted Answer · 2015-03-11 11:08:48Z

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.

answered Mar 11, 2015 at 11:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

MAK Over a year ago

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.

Community · Accepted Answer · 2017-05-23 11:43:23Z

1

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

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Mar 13, 2015 at 19:41

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

PostgreSQL 9.3: Split given string

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related