How to parse StreetAddresses String into individual fields

From: Raj Cherukuri <raj_strobe_at_yahoo.com>
Date: 9 Oct 2001 11:50:43 -0700
Message-ID: <e6ff1e18.0110091050.44538712@posting.google.com>

I have to take a string (from a data entry form) that contains Street Addresses such as (17824 NE 12TH ST APT 10-301) and have it parsed field by field and pass it into a PL/SQL stored procedured, format the string so that the individual fields are set to a specific column. For example  

FullAddress = '17824 NE 12TH ST APT 10-301' HouseNumber = 17824

Prefix = NE
Name   = 12TH
Type   = ST

UnitType = APT
Unit = 10-301

Once the fields are parsed column by column, have to reassemble them (by concatenation) and check against an existing string in the database. That is:

streetnm varchar2(75);
concatendated_address varchar2(75);
concatenated_address := HouseNumber || ' ' || Prefix || ' ' || Name || ' ' || Type || ' ' || UnitType || ' ' || unit; select fulladdress into streetnm from lisaddress where fulladdress like 'concatenated_address';
if rowcount > 0 then
  'execute another procedure that takes concatenated_address as a parameter
endif

Could you Show me the steps you do to accomplish this.

Thanks for your time,

Raj Received on Tue Oct 09 2001 - 13:50:43 CDT