Split the column value and make key as column name in postgres query-CodePudding

I have the table with the column value as below:

data_as_of_date:20210202 unique_cc:3999
data_as_of_date:20220202 unique_cc:1999

i need to convert this column into like this:

data_as_of_date    unique_cc
20210202           3999
20220202           1999

Sample data:

create table test (val varchar); 
insert into test(val) values ('data_as_of_date:20210202 unique_cc:3999'); 
insert into test(val) values ('data_as_of_date:20220202 unique_cc:1999');

I have tried with unnest with string_to_array & crosstab functions, but it is not working.

CodePudding user response：

You don't need unnest or a crosstab for this. A simple regular expression should do the trick:

select substring(the_column from 'data_as_of_date:([0-9]{8})') as data_as_of_date,
       substring(the_column from 'unique_cc:([0-9]{4})') as unqiue_cc
from the_table;