Any Way To Break Up An Address Into It's Components

[kwil38]

I have an address string (ex. Rural Route 12 Box 204) that I need to break into individual address components - i.e. Rural Route 12 as the street and 204 as the box.

The easiest way I can see to do this is to start reading the string at the beginning, somehow get the end position of the first set of numbers and consider everything before it to be the street (Rural Route 12).

Then start reading the string at the end of the string, somehow get the beginning position of the last set of numbers and consider everything after it to be the box (204).

What I can't figure out is how to accomplish this in PL/SQL. Any ideas??? Or better suggestion???

Thanks in advance!

 

[johnherman]

There are many companies who make products to help with such tasks. It's their bread and butter. Trillium, Ascential (IBM) Quality STage, Dun & Bradstreet, FirstLogic, etc.

If you think you want to re-invent or improve this process, I'll send you a few ideas on how companies like those do what they do as far as parsing and cleansing the data.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[carp]

You could iteratively find the location of the next blank, examine its predecessor/successor to see whether or not it's a number and make your break based on the results.
A better approach might be to find pairs of spaces and examine the bracketed characters to see if there are any numbers.

The problem is going to be how to handle things like

Joe Blow A12345B W 32 Street Suite 18 RR111 Duluth MI 12345-9011

With as many permutations "standard" addresses can take, you really have your work cut out for you!

 

[Dagon]

I had a spare moment, so I've written some example code;

CREATE OR REPLACE procedure split_address(p_address in varchar2, po_addr1 out varchar2, po_addr2 out varchar2) is
v_word       varchar2(50);
v_last_pos   integer := 0;
v_next_pos   integer;
v_numbers_found integer := 0;
v_address varchar2(50);
begin
   if substr(p_address, -1, 1) <> ' '
   then
      v_address := p_address||' ';
   else
      v_address := p_address;
   end if;
   loop
      v_next_pos := instr(v_address, ' ', instr(v_address, ' ', v_last_pos+1));
      v_word := substr(v_address, v_last_pos+1, v_next_pos - v_last_pos-1);
	  if v_word is null then
          goto end_loop;
      end if;
	  if translate(v_word, 'A0123456789', 'A') is null then
	    if v_numbers_found = 0 then
           po_addr1 := v_word;
           v_numbers_found := v_numbers_found + 1;
        else
		   po_addr2 := v_word;
        end if;
      end if;
      v_last_pos := v_next_pos;
   end loop;
   <<end_loop>>
   null;
end;
/

declare
  po_var1 varchar2(50);
  po_var2 varchar2(50);
begin
  split_address('Rural Route 12 Box 204', po_var1, po_var2);
  dbms_output.put_line('First Num: '||po_var1);
  dbms_Output.put_line('Second Num: '||po_var2);
end;
/