I have a column ‘zips’ with type ‘text’ in the table parcels.
User can fill either a single zip code, OR multiple comma separated zips, OR a range of zips separated by a hyphon.
Examples of possible datas are.
'10001'
'10002,10010,10015'
'10001,"10010-10025"'
I need to match the records with a zipcode ‘10015’.
eg:
select *
from parcels
where "10015" = ANY(string_to_array(parcels.zips, ','))
The Above code is working for the comma separated zips, but I am not sure about how to deal with the ranges.
I am looking for something like
select *
from parcels
where (
loop though `string_to_array(parcels.zips, ',')` and if iterating
variable contains '-', then 'where 10015 BETWEEN 10010 AND 10025'.
ELSE if zip doesn't contains '-', Then '10015' = '10001(other elements in the array)'
)
and combine the loop conditions with OR
3
Answers
You can unnest the elements of the column and use them in an EXIST condition that checks for ranges:
I would put this into a function to make that easier:
Then it is as easy as:
Online example
try this :
try to get your data in the format you need them, with some CTEs: