Crazy data query

 have an database column that is like the below and not normalized. So the entire address in in on field.

Below is a sample of the data

There is a space between each item.

4918 HULK PKY *LOT 34C BUGTUSEL GA
4932 HULK PL BUGTUSEL GA 30000
205 SPIDER ST SAINT JOHN GA 00000

4027 LEE-OAKS PL *LOT 61C, SAVOY CHASE
4027 LEE-OAKS PL *LOT 61C, SAVOY CHASE
4033 LEE-OAKS PL *LOT 60C, SVOY CHASE
4033 LEE-OAKS PL *LOT 60C, SVOY CHASE
4085 LEE-OAKS PL, SAVOY CHASE CROSSING

Since the data is not consistent I would like to extract each item into

I am trying to do something outside of PL/SQL with SQL

The second record is the ideal record for this column with a zip code.

Note that LOT XXX should be part of the STREET in this senario

I am trying to utilize INSTR, STR and 10g REGEXP to strip each record into separate columns. Received on Mon Sep 22 2008 - 16:45:09 CDT