I need to write a SQL to match a given zip code from table containing a range of zip codes which are defined as comma delimited or as zip code values.
DB Version: oracle 19c . ID column is PK
Sample Code to create the table
CREATE TABLE TEST_ZIP ( ID INTEGER,
ZIP_VALUES CLOB
);
INSERT INTO TEST_ZIP
VALUES(1,'79049-79051,79087,80808-80811,80813,80817,80821,80823,80825,80829-80833,');
INSERT INTO TEST_ZIP
VALUES(1,'84637,84644,84657-84658,84685-84715,84718-84733,84735-84772,84774,84776-84896,86021-86022,');
INSERT INTO TEST_ZIP
VALUES(1,'80820,81097-81220,81222-81225,81227,81230-81239,81241-81243,81247-81249,81252,81278-81311,81326,81328-81329,81332-81333,');
Table Data: TEST_ZIP
ID ZIP_VALUES
1 79049-79051,79087,80808-80811,80813,80817,80821,80823,80825,80829-80833,
2 84637,84644,84657-84658,84685-84715,84718-84733,84735-84772,84774,84776-84896,86021-86022,
3 80820,81097-81220,81222-81225,81227,81230-81239,81241-81243,81247-81249,81252,81278-81311,81326,81328-81329,81332-81333,
Expected Result:
ZIP_CODE = 79049
We should get row with ID =1 since this is found in range 79049-79051
ZIP_CODE = 84637
We should get row with ID =2 since this is found as a stand alone zip 84637 on row 2
ZIP_CODE = 81248
We should get row with ID =3 since this is found with zip 81247-81249 on row 3
Thanks much in Advance
Kevin