changing a range into discrete values for a field in oracle

Question

I have a data table that contains records like this:

ZIP       Ground  Air
010-041   005     305
150-163   004     304

As I have it right now, it's actually an excel file but I eventually need to convert it into a table in Oracle. The problem is that the format of the first column is a range but I need it as discrete values i.e.

ZIP

10    005     305
11    005     305
12    005     305
13    005     305
14....

What's the easiest way to accomplish this in excel and/or oracle?

Answer 1

Assuming you have inserted the data into Oracle perhaps using an external table, you could use the connect by clause of a hierarchical query to generate the additional rows you need.

Setup to simulate the source data:

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (Zip Varchar2(7), Ground Varchar2(3), Air Varchar2(3));
INSERT INTO t1 VALUES ('010-041','005','305');
INSERT INTO t1 VALUES ('150-163','004','304');

Solution:

CREATE TABLE t2 AS (
  SELECT substr(zip,1,3) + Level - 1 Zip, Ground, Air 
  FROM t1 a CONNECT BY level <= substr(zip,5,3)-substr(zip,1,3)
  );

---

If you'd rather do more work on the Excel side, then you could create the following table in Oracle:

CREATE TABLE t1 (Zip Number(3), Ground Varchar2(3), Air Varchar2(3));

Then run a formula like this for each row in Excel:

=CONCATENATE("INSERT INTO t1 (SELECT ",MID(A2,1,3),
    "+Level-1,'",B2,"','",C2,"' FROM dual connect by level <=",MID(A2,5,3)-MID(A2,1,3),");")

This will produce insert statements that you can then run in Oracle. They will look like this:

INSERT INTO t1 (SELECT 010+Level-1,'005','305' FROM dual connect by level <=31);

Answer 2

May I also suggest that, depending on how you will use the table, you don't necessarily need each potential value in its own row. You can join on ranges:

SELECT T.Something, Z.Ground, Z.Air
FROM
   Table T
   INNER JOIN ZipRanges Z
      ON T.Zip BETWEEN Z.StartZip AND Z.EndZip

This should be highly performant with the proper indexes, and saves you potentially a bunch of hassle dealing with all the different values. I'd personally much rather keep the data in its compact form if at all possible.

Answer 3

Follow the steps below -

1. Create a temporary table PS_TEST with one field name UN_RANGED_ZIPCODES.
2. Insert the number range expansion into this table. e.g. I needed to un-range from 00001 to 99999. I inserted all the numbers in the text format from 00001 to 99999 (created insert script using excel).
3. Use the sample query below. You would need to modify query according to your table structure. In this case the table PS_GEOG_LOCN_RANGE contains the data which need to be un-ranged.

Query:

select t.UN_RANGED_ZIPCODES, a.* from PS_GEOG_LOCN_RANGE a, PS_TEST t  
where  a.EFFDT = (select max(a1.effdt) from PS_GEOG_LOCN_RANGE a1 
                  where a1.LOCATION_TBL_ID = a.LOCATION_TBL_ID 
                  and a1.effdt <= SYSDATE 
                )  
and to_number(t.UN_RANGED_ZIPCODES) between to_NUMBER(a.LOCN_FROM) and  to_NUMBER( substr(a.LOCN_TO, 1,5)   ) 
 AND a.LOCATION_TBL_ID IN ( 
 'XYZ', 
 'ABC')

Result - If from location (LOCN_FROM) contained 99805 and to location (LOCN_TO) contained 99911. It returned me all the numbers between from and to locations.