skip to Main Content

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


  1. You can unnest the elements of the column and use them in an EXIST condition that checks for ranges:

    select *
    from parcels p
    where exists (select *
                  from (
                      select split_part(trim(both '"' from z.zip), '-', 1) as from_zip,
                             split_part(trim(both '"' from z.zip), '-', 2) as to_zip
                      from unnest(string_to_array(p.zip_codes, ',')) as z(zip)
                  ) x
                  where (x.to_zip = '' and x.from_zip = '10015')
                     or (x.to_zip <> '' and '10015' between x.from_zip and coalesce(x.to_zip, '10015'))
                  ); 
    

    I would put this into a function to make that easier:

    create function contains_zip(p_codes text, p_zip_code text)
      returns boolean
    as
    $$
      select exists 
        (select *
         from (
           select split_part(trim(both '"' from z.zip), '-', 1) as from_zip,
                  split_part(trim(both '"' from z.zip), '-', 2) as to_zip
           from unnest(string_to_array(p_codes, ',')) as z(zip)
         ) x
         where (x.to_zip = '' and x.from_zip = p_zip_code)
            or (x.to_zip <> '' and p_zip_code between x.from_zip and coalesce(x.to_zip, p_zip_code))
        );
    $$
    language sql
    immutable;
    

    Then it is as easy as:

    select *
    from parcels p
    where contains_zip(p.zip_codes, '10015');   
    

    Online example

    Login or Signup to reply.
  2. try this :

    SELECT * 
    FROM parcels p
    CROSS JOIN LATERAL regexp_split_to_table (p.zips, ',') AS z
    WHERE CASE
            WHEN strpos (z, '-') > 0
            THEN '10015' BETWEEN split_part (z, '-', 1) AND split_part (z, '-', 2)
            ELSE z = '10015'
          END 
    
    Login or Signup to reply.
  3. try to get your data in the format you need them, with some CTEs:

    with _data as (
    select * from (values(1,'10001'),(2,'10002,10010,10015'), (3,'10001,"10010-10025"')) as _vals (i,x)
    ),
    
    _data2 as ( 
    select 
    i,
    unnest(string_to_array(x,','))as x
    from _data
    ),
    
    _data3 as (
    select
    i,
    x,
    replace(split_part(x,'-',1),'"','') as x1,
    replace(split_part(x,'-',2),'"','') as x2
    from  _data2
    )
    
    select * from _data3
    where 
    case when x2 = '' then x1::int = 10015 end
    or 
    case when x2 <> '' then 10015 between(x1::int) and (x2::int) end
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search