CODEWITHSUNDEEP
sql
Bailey Smith
Bailey Smith

Reputation: 1

Missing zip codes in a range

I have a zip code table that has two columns (startzip) and (endzip). The start zip code is always different from the end zip code but it is always range of at least 5 zip codes.

For example, start zip is 07002 and the endzip is 07007. I need to identify the other missing zips within that range. So I would need 07003, 07004, 07005, 07006.

How can I achieve this in SQL?

Upvotes: 0

Views: 98

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

You dont mention DBMS but these days almost all support recursive CTEs. Example for postgres:

with recursive t (n) as (
    values (cast('07002' as int))
    union all
    select n+1 from t where n < cast('07007' as int)
)
select TO_CHAR(n, 'fm00000') from t;

07002
07003 
07004
07005
07006
07007

See Fiddle

Upvotes: 1

Related Questions