split a string by comma in postgres

Question

There is a table in my postgres database that contains 4 digit years in a string format concat with each other -

rollno | year
-----------------------------------
00001  | 201220132014
-----------------------------------
00002  | 2013201420152016
-----------------------------------
00003  | 2015
-----------------------------------

I have above data in my postgres table, I want my data to be separated by comma as below example :

rollno | year
-----------------------------------
00001  | 2012,2013,2014
-----------------------------------
00002  | 2013,2014,2015,2016
-----------------------------------
00003  | 2015
-----------------------------------

Fix your design. Normalize your tables instead of looking for complicated queries — Gurwinder Singh
– Gurwinder Singh, Commented Jan 12, 2017 at 5:28

Tim Biegeleisen · Accepted Answer · 2017-01-12 05:43:38Z

1

You can use the regex (\d)(?=(\d{4})+$) with \1, as replacement.

update yourTable
set year = regexp_replace(year, '(\d)(?=(\d{4})+$)', '\1,', 'g')

However, this query is actually taking you in the wrong direction, because it results in having CSV data in your year column. Instead, each year should ideally be in a separate record to keep your database normalized.

edited Jan 12, 2017 at 5:43

answered Jan 12, 2017 at 5:29

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

Dhairya Lakhera Over a year ago

No it does not make it comma separated still. It just return the same string

Tim Biegeleisen Over a year ago

I tested this locally on my Postgres and it works. What type is your year column? Is it numeric or text?

Dhairya Lakhera Over a year ago

Its character varying

Tim Biegeleisen Over a year ago

It runs for me without any problems, I cannot help you further.

user330315 Over a year ago

@Curious: Tim's solution works, see here: rextester.com/SYOP26824 If it doesn't work for you then you probably forgot to tell some details.

Vivek S. · Accepted Answer · 2017-01-12 08:58:13Z

1

Try this

update tbl1
set vl = t.yr
from (
    select id
        ,string_agg(y, ',') yr
    from (
        select id
            ,unnest(regexp_split_to_array(vl, E '(?=(....)+$)')) y
        from tbl1
        ) s
    group by id
    ) t
where t.id = tbl1.id

edited Jan 12, 2017 at 8:58

answered Jan 12, 2017 at 5:57

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

split a string by comma in postgres

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related