Parsing out inconsistent data to grab specific year

Question

I have a TableA with a column Years. Each row value in this column is formatted as a mix of:

2021;2017;2019;2022

and

2021,2017,2019

The number of years listed in each row varies (could be 1, could be 10).

I have a task to pull the latest year for each row. I am not even sure where to start on this, so I haven't tried anything as of yet.

Any ideas?

Have a look at this [thread](https://stackoverflow.com/questions/42736131/postgresql-opposite-of-string-agg). This will let you parse both options, now you just need choose which of the two options you'll need. Try one, if it fails, try the second option. — Mushroomator, Sep 22 '22 at 21:08

score 1 · Accepted Answer · answered Sep 22 '22 at 21:50

1

Use unnest(string_to_array(...)) to convert the values to rows, then take the max of the expanded column:

select ID, max(year) as max_year
from (select id, unnest(string_to_array(Years, ';')) as year from TableA) x
group by ID

answered Sep 22 '22 at 21:50

Bohemian

1 Answers1