1

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
MAK
  • 6,824
  • 25
  • 74
  • 131

2 Answers2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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. – MAK Mar 11 '15 at 11:22
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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228