Parsing out inconsistent data to grab specific year

JD2775 0
Viewed 19

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?

sql postgresql
1 Answers

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

See live demo.

Bohemian0
Related