Sproc for Parsing Address String into Individual Address Elements
laurenq...@gmail.com
created this sproc. The sporoc returns int IDs rather than str Names
since that is the requirment on the front end application, but it can
be easily modified.
This sproc is far from elegant, first version, but it does the trick
pretty well. If I had another 20 hours I'd play with it and streamline
it but I toss it up here inc ase anyone else has a need for such
functionality and/or someone wants to tinker and improve the beast.
Regards,
lq
(I did not put the drop table code in here - you will need to REM the
table creation code after 1st run)
-- ==========================================================
-- USAGE: Called from application code passing one string parameter
-- containing the full address string @strAddressFull nvarchar(255)
-- NOTE: 1.) The address string parameter must contain only single
spaces
-- and no leading and trailing spaces.
-- 2.) The string paramter may contain periods and commas though
-- these are irrelevant in the parsing scheme.
-- 3.) The string parameter must not contain carriage returns.
-- VERSION: 1.0
-- PURPOSE: Parses an address string to extract seperate elements
-- AUTHOR: Lauren Quantrell
-- DATE: Feb 12 2009
-- ==========================================================
Alter Procedure ParseAddress
--@strAddressFull nvarchar(255)
AS
SET NOCOUNT ON
-- *****************************************************************
-- for testing purposes:
DECLARE @strAddressFull nvarchar(255)
set @strAddressFull = '1401 Fremont Ave. 5th Floor South Pasadena,
CA 91030'
-- set @strAddressFull = '1234 Turney Drive Apt. 9A, Mississauga,
Ontario L5M 2P9'
--set @strAddressFull = 'Via Michelangelo 22 Siena Tuscany 53100
ITALY'
--set @strAddressFull = 'Via Encanta 67A Castelletto sopra Ticino
Piemonte'
--set @strAddressFull = 'Via Encanta 67A Castelletto sopra Ticino
ITALY'
--******************************************************************
--******************************************************************
--******************************************************************
-- create these tables for your own testing.
-- in production use these tables would be fully populated
-- NOTE: tblGEO_SubRegions is not created here. Values are shown to
demonstrate further
-- possible linkages...
CREATE TABLE tblGEO_Cities
(
City NVARCHAR(50) NOT NULL,
Admin1ID INTEGER NOT NULL,
GeoNationID INTEGER NOT NULL,
CityID INTEGER NOT NULL primary key identity
)
insert into tblGEO_Cities values ('Pasadena',1,3)
insert into tblGEO_Cities values ('South Pasadena',1,3)
insert into tblGEO_Cities values ('Arcadia',1,3)
insert into tblGEO_Cities values ('Siena',3,2)
insert into tblGEO_Cities values ('Rome',4,2)
insert into tblGEO_Cities values ('Castelletto sopra Ticino',5,2)
insert into tblGEO_Cities values ('Mississauga',6,1)
insert into tblGEO_Cities values ('Toronto',6,1)
insert into tblGEO_Cities values ('Vancouver',7,1)
insert into tblGEO_Cities values ('Seattle',8,3)
insert into tblGEO_Cities values ('Annapolis',2,3)
insert into tblGEO_Cities values ('Cape St. Claire',2,3)
-- NOTE: My production tblGEO_Cities has 139,475 cities
CREATE TABLE tblGEO_Admin1
(
Admin1Name NVARCHAR(60) NOT NULL,
GeoNationID INTEGER NOT NULL,
Abbreviation NVARCHAR(10) NOT NULL,
Short NVARCHAR(10) NOT NULL,
GeoSubRegionID INTEGER NOT NULL,
Admin1ID INTEGER NOT NULL primary key identity
)
insert into tblGEO_Admin1 values ('California',3,'CA','Calif',1)
insert into tblGEO_Admin1 values ('Maryland',3,'MD','MD',3)
insert into tblGEO_Admin1 values ('Tuscany',2,'TUC','Tusc',5)
insert into tblGEO_Admin1 values ('Lazio',2,'LAZ','Laz',5)
insert into tblGEO_Admin1 values ('Piemonte',2,'PDE','Piedmt',5)
insert into tblGEO_Admin1 values ('Ontario',1,'ON','Ont',4)
insert into tblGEO_Admin1 values ('British Columbia',1,'BC','BC',6)
insert into tblGEO_Admin1 values ('Washington',3,'WA','Wash',2)
-- NOTE: My production tblGEO_Admin1 has 3988 Admin1 listings (ie:
states/provinces)
CREATE TABLE tblGEO_Nations
(
Nation NVARCHAR(60) NOT NULL,
GeoNationID INTEGER NOT NULL primary key identity
)
insert into tblGEO_Nations values ('Canada')
insert into tblGEO_Nations values ('Italy')
insert into tblGEO_Nations values ('United States')
-- NOTE: My production tblGEO_Nations has 262 nations
CREATE TABLE tblGeo_Zips
(
ZipCode NVARCHAR(10) NOT NULL,
CityID INTEGER NOT NULL,
ZipID INTEGER NOT NULL primary key identity
)
insert into tblGeo_Zips values ('21401',11)
insert into tblGeo_Zips values ('21401',12)
insert into tblGeo_Zips values ('91030',2)
insert into tblGeo_Zips values ('21403',11)
-- NOTE: My production tblGeo_Zips has 46,745 US zip codes
CREATE TABLE tblAddressApt
(
AddressApt NVARCHAR(20) NOT NULL,
ID INTEGER NOT NULL primary key identity
)
insert into tblAddressApt values ('Apt')
insert into tblAddressApt values ('Apt.')
insert into tblAddressApt values ('Floor')
insert into tblAddressApt values ('Flr.')
insert into tblAddressApt values ('Bldg')
insert into tblAddressApt values ('Bldg.')
insert into tblAddressApt values ('Suite')
insert into tblAddressApt values ('Ste')
insert into tblAddressApt values ('Ste.')
-- NOTE: My production tblAddressApt has 52 sub-address types
CREATE TABLE tblAddressSuffix
(
AddressApt NVARCHAR(20) NOT NULL,
ID INTEGER NOT NULL primary key identity
)
insert into tblAddressSuffix values ('Ave')
insert into tblAddressSuffix values ('Ave.')
insert into tblAddressSuffix values ('Avenue')
insert into tblAddressSuffix values ('St')
insert into tblAddressSuffix values ('St.')
insert into tblAddressSuffix values ('Street')
insert into tblAddressSuffix values ('Way')
insert into tblAddressSuffix values ('Blvd')
insert into tblAddressSuffix values ('Blvd.')
insert into tblAddressSuffix values ('Boulevard')
-- NOTE: My production tblAddressSuffix has 561 suffix types
--******************************************************************
--******************************************************************
--******************************************************************
-- looping declarations:
DECLARE @strAddress nvarchar(255)
DECLARE @intSpaceLocator int
DECLARE @strExtract nvarchar(255)
DECLARE @intWords int
DECLARE @intWordsCountdown int
DECLARE @strWordCount nvarchar(255)
SELECT @strWordCount = @strAddressFull
SET @intWords = LEN(@strWordCount) - LEN(REPLACE(@strWordCount,'
','')) + 1
-- country looping declarations:
DECLARE @intNationCount int
DECLARE @strNation nvarchar(50)
DECLARE @intNationWords tinyint
-- city looping declarations:
DECLARE @intCityCount int
DECLARE @intCityWords smallint
-- zip looping declarations:
DECLARE @intZipCount int
-- apt looping declarations:
DECLARE @AptID int
DECLARE @intAptPosition int
DECLARE @strAptNumber varchar(255)
DECLARE @intSuffixID int
-- return value declarations:
DECLARE @ZipID int
DECLARE @ZipCode nvarchar(10)
DECLARE @CityID int
DECLARE @City nvarchar(50)
DECLARE @Admin1ID int
DECLARE @GeoNationID int
DECLARE @GeoSubregionID int
DECLARE @RegionID int
DECLARE @ConAddress nvarchar(255)
DECLARE @ConSuiteAptFloor varchar(15)
DECLARE @ConInCareOf varchar(50)
DECLARE @StreetSuffix varchar(50)
-- pre-search the end of the string for nation name United States
variants:
IF RIGHT(@strAddressFull, 3) = ' US'
BEGIN
SELECT @GeoNationID = 3
SELECT @strAddressFull = SUBSTRING(@strAddressFull, 1, LEN
(@strAddressFull) - 3)
SET @intWords = @intWords - 1
END
IF RIGHT(@strAddressFull, 5) = ' U.S.'
BEGIN
SELECT @GeoNationID = 3
SELECT @strAddressFull = SUBSTRING(@strAddressFull, 1, LEN
(@strAddressFull) - 5)
SET @intWords = @intWords - 1
END
IF RIGHT(@strAddressFull, 4) = ' USA'
BEGIN
SELECT @GeoNationID = 3
SELECT @strAddressFull = SUBSTRING(@strAddressFull, 1, LEN
(@strAddressFull) - 4)
SET @intWords = @intWords - 1
END
IF RIGHT(@strAddressFull, 7) = ' U.S.A.'
BEGIN
SELECT @GeoNationID = 3
SELECT @strAddressFull = SUBSTRING(@strAddressFull, 1, LEN
(@strAddressFull) - 7)
SET @intWords = @intWords - 1
END
IF RIGHT(@strAddressFull, 14) = ' United States'
BEGIN
SELECT @GeoNationID = 3
SELECT @strAddressFull = SUBSTRING(@strAddressFull, 1, LEN
(@strAddressFull) - 14)
SET @intWords = @intWords - 2
END
IF RIGHT(@strAddressFull, 25) = ' United States of America'
BEGIN
SELECT @GeoNationID = 3
SELECT @strAddressFull = SUBSTRING(@strAddressFull, 1, LEN
(@strAddressFull) - 25)
SET @intWords = @intWords - 4
END
-- check for any country name:
IF @GeoNationID IS NULL
-- United States variants were not found in pre-screening
@strAddressFull:
BEGIN
-- pre-search any part of the string to match the country:
-- count the number of records in dbo.tblGEO_Nations:
==============================
SELECT @intNationCount = COUNT(*) FROM dbo.tblGEO_Nations
SELECT @intNationCount = @intNationCount + 100 --(NOTE: 100 is
the first PK_ID)
-- loop through each nation in dbo.tblGEO_Nations to find a match:
WHILE @intNationCount > = 100
BEGIN
SELECT @strNation = (SELECT n.Nation FROM dbo.tblGEO_Nations n
WHERE n.GeoNationID = @intNationCount)
IF @strAddressFull LIKE '%' + @strNation + '%'
BEGIN
SELECT @GeoNationID = (SELECT n.GeoNationID FROM
dbo.tblGEO_Nations n WHERE n.GeoNationID = @intNationCount)
-- remove the nation string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull, ' ' +
@strNation, '')
-- count the number of words in Nation:
SELECT @strWordCount = @strNation
SELECT @intNationWords = LEN(@strWordCount) - LEN(REPLACE
(@strWordCount,' ','')) + 1
-- remove the number of words in the nation name from @intWords:
SET @intWords = @intWords - @intNationWords
END
SET @intNationCount = @intNationCount - 1
END
END
-- create the address temp table:
CREATE TABLE #addressTEMP
(
ID int identity,
strTemp nvarchar(255)
)
-- create the zip temp table:
CREATE TABLE #zipTEMP
(
ID int identity,
ZipID int,
CityID int,
City nvarchar(50),
Admin1ID int,
Abbreviation nvarchar(50),
GeoNationID int,
GeoSubregionID int
)
-- start the loop through @strAddress:
SET @intWordsCountdown = @intWords
SET @strAddress = @strAddressFull
WHILE @intWordsCountdown > 0
BEGIN
-- find the first occurance of a space:
SET @intSpaceLocator = PATINDEX( '% %' ,@strAddress )
-- extract the first word:
IF @intSpaceLocator > 0
BEGIN
SET @strExtract = SUBSTRING(@strAddress,1,@intSpaceLocator - 1)
END
ELSE
BEGIN
SET @strExtract = @strAddress
END
-- clean up the extracted word:
IF RIGHT(@strExtract,1) = ',' OR RIGHT(@strExtract,1) = '.'
BEGIN
SELECT @strExtract = LEFT(@strExtract,LEN(@strExtract)-1)
END
-- insert the word into the address temp table:
INSERT INTO #addressTEMP
(
strTemp
)
SELECT
@strExtract
-- remove the just-extracted word from full string:
IF @intSpaceLocator > 0
BEGIN
SET @strAddress = SUBSTRING(@strAddress, @intSpaceLocator + 1,
255)
END
-- start zip code search:
IF ISNUMERIC(LEFT(@strExtract,5)) = 1 AND @ZipCode IS NULL AND
(@GeoNationID = 3 OR @GeoNationID IS NULL) AND @intWordsCountdown <
@intWords
-- string is numeric AND zip code is NULL AND (nation is United
States or not yet determined) AND not extracting the first word (which
is most likely the street number):
BEGIN
-- insert the zip into the zip temp table if word matches zip
code:
INSERT INTO #zipTEMP
(
ZipID,
CityID,
City,
Admin1ID,
Abbreviation,
GeoNationID,
GeoSubregionID
)
SELECT
z.ZipID,
z.CityID,
c.City,
z.Admin1ID,
a.Abbreviation,
a.GeoNationID,
a.GeoSubregionID
FROM
dbo.tblGeo_Zips z
LEFT OUTER JOIN dbo.tblGEO_Cities c ON z.CityID = c.CityID
LEFT OUTER JOIN dbo.tblGEO_Admin1 a ON z.Admin1ID = a.Admin1ID
WHERE
(z.ZipCode = LEFT(@strExtract,5))
-- count the number of zip codes records found:
SELECT @intZipCount = COUNT(*) FROM #zipTEMP
IF @intZipCount >= 1
-- one or more zip code matched cities were located:
BEGIN
SELECT @ZipID = (SELECT TOP 1 ZipID FROM #zipTEMP)
SELECT @ZipCode = @strExtract
SELECT @Admin1ID = (SELECT TOP 1 Admin1ID FROM #zipTEMP WHERE
ZipID = @ZipID)
SELECT @GeoNationID = (SELECT TOP 1 GeoNationID FROM #zipTEMP
WHERE ZipID = @ZipID)
SELECT @GeoSubregionID = (SELECT TOP 1 GeoSubregionID FROM
#zipTEMP WHERE ZipID = @ZipID)
IF @intZipCount = 1
-- one city was found matching the zip code:
BEGIN
SELECT @CityID = (SELECT TOP 1 CityID FROM #zipTEMP WHERE
ZipID = @ZipID)
SELECT @City = (SELECT TOP 1 City FROM #zipTEMP WHERE ZipID
= @ZipID)
-- remove the city name string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull, ' ' +
@City, '')
END
-- remove the zipcode string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull, ' ' +
@strExtract, '')
-- remove the zipcode from #addressTEMP:
DELETE FROM #addressTEMP WHERE (#addressTEMP.strTemp =
@strExtract)
IF @Admin1ID IS NOT NULL
BEGIN
-- remove the Admin1 Abbreviation string from
@strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull, ' ' +
(SELECT TOP 1 Abbreviation
FROM dbo.tblGEO_Admin1
WHERE (dbo.tblGEO_Admin1.Admin1ID = @Admin1ID)
), '')
-- remove the Admin1 Name string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull , ' ' +
(SELECT TOP 1 Admin1Name
FROM dbo.tblGEO_Admin1
WHERE (dbo.tblGEO_Admin1.Admin1ID = @Admin1ID)
), '')
-- remove the Short Name string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull , ' ' +
(SELECT TOP 1 Short
FROM dbo.tblGEO_Admin1
WHERE (dbo.tblGEO_Admin1.Admin1ID = @Admin1ID)
), '')
END
END
END
IF ISNUMERIC(@strExtract) = 0
BEGIN
-- @strExtract is not numeric - look for apt:
SELECT @AptID = (SELECT ID FROM dbo.tblAddressApt WHERE
AddressApt = @strExtract)
IF @AptID IS NOT NULL
BEGIN
SELECT @ConSuiteAptFloor = @strExtract
SELECT @intAptPosition = @intWords - @intWordsCountdown + 1
-- remove the apt string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull, ' ' +
@strExtract, '')
END
END
-- incremement the loop:
SET @intWordsCountdown = @intWordsCountdown - 1
END
-- try to match unmatched Admin1ID:
IF @Admin1ID IS NULL
BEGIN
-- try to locate on Admin1 abbreviation:
SELECT @Admin1ID = (
SELECT TOP 1 Admin1ID
FROM dbo.tblGEO_Admin1
JOIN #addressTEMP ON dbo.tblGEO_Admin1.Abbreviation =
#addressTEMP.strTemp
WHERE (dbo.tblGEO_Admin1.GeoNationID = @GeoNationID OR
@GeoNationID IS NULL)
)
IF @Admin1ID IS NOT NULL
BEGIN
-- remove the Admin1 Abbreviation string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull, ' ' + (SELECT
TOP 1 Abbreviation
FROM dbo.tblGEO_Admin1
WHERE (dbo.tblGEO_Admin1.Admin1ID = @Admin1ID)
), '')
-- remove the Admin1 Abbreviation from #addressTEMP:
DELETE FROM #addressTEMP WHERE (#addressTEMP.strTemp = (SELECT
TOP 1 Abbreviation FROM dbo.tblGEO_Admin1 WHERE Admin1ID = @Admin1ID))
END
ELSE
BEGIN
-- try to locate on Admin1 name:
SELECT @Admin1ID = (
SELECT TOP 1 Admin1ID
FROM dbo.tblGEO_Admin1
JOIN #addressTEMP ON dbo.tblGEO_Admin1.Admin1Name =
#addressTEMP.strTemp
WHERE (dbo.tblGEO_Admin1.GeoNationID = @GeoNationID OR
@GeoNationID IS NULL)
)
IF @Admin1ID IS NOT NULL
BEGIN
-- remove the Admin1 Name string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull , ' ' +
(SELECT TOP 1 Admin1Name
FROM dbo.tblGEO_Admin1
WHERE (dbo.tblGEO_Admin1.Admin1ID = @Admin1ID)
), '')
-- remove the Admin1Name from #addressTEMP:
DELETE FROM #addressTEMP WHERE (#addressTEMP.strTemp = (SELECT
TOP 1 Admin1Name FROM dbo.tblGEO_Admin1 WHERE Admin1ID = @Admin1ID))
END
ELSE
BEGIN
-- try to locate on Short name:
SELECT @Admin1ID = (
SELECT TOP 1 Admin1ID
FROM dbo.tblGEO_Admin1
JOIN #addressTEMP ON dbo.tblGEO_Admin1.Short =
#addressTEMP.strTemp
WHERE (dbo.tblGEO_Admin1.GeoNationID = @GeoNationID OR
@GeoNationID IS NULL)
)
IF @Admin1ID IS NOT NULL
BEGIN
-- remove the Short Name string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull , ' ' +
(SELECT TOP 1 Short
FROM dbo.tblGEO_Admin1
WHERE (dbo.tblGEO_Admin1.Admin1ID = @Admin1ID)
), '')
-- remove the short from #addressTEMP:
DELETE FROM #addressTEMP WHERE (#addressTEMP.strTemp =
(SELECT TOP 1 Short FROM dbo.tblGEO_Admin1 WHERE Admin1ID =
@Admin1ID))
END
END
END
END
-- try to match unmatched @GeoNationID:
IF @GeoNationID IS NULL
BEGIN
IF @Admin1ID IS NOT NULL
BEGIN
-- extract GeoNationID from dbo.tblGEO_Admin1:
SELECT @GeoNationID = (
SELECT GeoNationID
FROM dbo.tblGEO_Admin1
WHERE (Admin1ID = @Admin1ID)
)
END
ELSE
BEGIN
-- try to match nation with words in #addressTEMP:
SELECT @GeoNationID = (
SELECT GeoNationID
FROM dbo.tblGEO_Nations
JOIN #addressTEMP ON dbo.tblGEO_Nations.Nation =
#addressTEMP.strTemp
)
END
END
-- try to match unmatched city:
IF @CityID IS NULL AND @intZipCount > 0
-- zip code was located (only occurs if multiple cities exist for
matched zip code):
BEGIN
SELECT @ZipID = (
SELECT TOP 1 ZipID
FROM #zipTEMP
LEFT OUTER JOIN #addressTEMP ON #zipTEMP.City =
#addressTEMP.strTemp
WHERE
#addressTEMP.ID > 0
)
IF @ZipID > 0
BEGIN
SELECT @CityID = (SELECT TOP 1 CityID FROM #zipTEMP WHERE ZipID
= @ZipID)
-- remove the city string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull , ' ' + (SELECT
TOP 1 City FROM #zipTEMP WHERE ZipID = @ZipID), '')
-- remove the city row from #addressTEMP:
DELETE FROM #addressTEMP WHERE (#addressTEMP.strTemp = (SELECT
TOP 1 City FROM dbo.tblGEO_Cities WHERE CityID = @CityID))
END
END
ELSE IF @CityID IS NULL AND @intZipCount IS NULL
BEGIN
-- try to match city in #addressTEMP (will only work on one-word
cities):
SELECT @CityID = (
SELECT TOP 1 c.CityID FROM dbo.tblGEO_Cities c
JOIN #addressTEMP x ON c.City = x.strTemp
WHERE (c.GeoNationID = @GeoNationID OR @GeoNationID IS NULL)
AND (c.Admin1ID = @Admin1ID OR @Admin1ID IS NULL)
ORDER BY c.Population DESC
)
IF @CityID IS NOT NULL
BEGIN
-- remove the city string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull , ' ' + (SELECT
TOP 1 City FROM dbo.tblGEO_Cities WHERE CityID = @CityID), '')
-- remove the city row from #addressTEMP:
DELETE FROM #addressTEMP WHERE (#addressTEMP.strTemp = (SELECT
TOP 1 City FROM dbo.tblGEO_Cities WHERE CityID = @CityID))
END
END
IF @CityID IS NULL
-- all attempts to match city have failed so query
dbo.tblGEO_Cities:
BEGIN
-- create the city temp table:
CREATE TABLE #cityTEMP
(
ID int identity,
CityID int,
City nvarchar(50)
)
INSERT INTO #cityTEMP
(
CityID,
City
)
SELECT
CityID,
City
FROM
dbo.tblGEO_Cities
WHERE
(Admin1ID = @Admin1ID OR @Admin1ID IS NULL)
AND (GeoNationID = @GeoNationID OR @GeoNationID IS NULL)
-- count the number of records in #cityTEMP:
==============================
SELECT @intCityCount = COUNT(*) FROM #cityTEMP
-- loop through each city in #cityTEMP to find a match:
WHILE @intCityCount > 0
BEGIN
SELECT @City = (SELECT City FROM #cityTEMP WHERE ID =
@intCityCount)
IF @strAddressFull LIKE '%' + @City + '%'
-- matched a city:
BEGIN
SELECT @CityID = (SELECT CityID FROM #cityTEMP WHERE ID =
@intCityCount)
-- remove the city string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull, ' ' + @City,
'')
IF @Admin1ID IS NULL
BEGIN
SELECT @Admin1ID = (SELECT Admin1ID FROM dbo.tblGeo_Cities
WHERE CityID = @CityID)
END
IF @GeoNationID IS NULL
BEGIN
SELECT @GeoNationID = (SELECT GeoNationID FROM
dbo.tblGeo_Cities WHERE CityID = @CityID)
END
END
SET @intCityCount = @intCityCount - 1
END
END
-- finish apt number if any:
IF @ConSuiteAptFloor IS NOT NULL
BEGIN
-- get the apt number:
IF @ConSuiteAptFloor = 'FL' OR @ConSuiteAptFloor = 'FL.' OR
@ConSuiteAptFloor = 'FLOOR' OR @ConSuiteAptFloor = 'FLR' OR
@ConSuiteAptFloor = 'FLR.'
BEGIN
-- extract the word before the apt type in #addressTEMP:
SELECT @strAptNumber = (SELECT strTemp FROM #addressTEMP WHERE
ID = @intAptPosition - 1)
-- test the word to see if it is an address suffix:
SELECT @intSuffixID = (SELECT ID FROM dbo.tblAddressSuffix
WHERE AddressSuffix = @strAptNumber)
IF @intSuffixID IS NOT NULL
-- the previous word was a street suffix not a floor number:
BEGIN
-- extract the word after the apt type in #addressTEMP:
SELECT @strAptNumber = (SELECT strTemp FROM #addressTEMP WHERE
ID = @intAptPosition + 1)
END
ELSE
-- even though word before floor was not a street suffix, test
the word after floor just to make sure:
BEGIN
SELECT @strAptNumber = (SELECT strTemp FROM #addressTEMP WHERE
ID = @intAptPosition + 1)
IF ISNUMERIC(@strAptNumber) = 0
-- the word after floor is not numeric so go with the word
before:
BEGIN
SELECT @strAptNumber = (SELECT strTemp FROM #addressTEMP
WHERE ID = @intAptPosition - 1)
END
END
END
ELSE
BEGIN
SELECT @strAptNumber = (SELECT strTemp FROM #addressTEMP WHERE
ID = @intAptPosition + 1)
END
IF @strAptNumber IS NOT NULL
-- add the apartment number to the apartment type:
BEGIN
-- remove the AptNumber string from @strAddressFull:
SELECT @strAddressFull = REPLACE(@strAddressFull , ' ' +
@strAptNumber, '')
IF RIGHT(@strAptNumber, 2) = 'st' OR RIGHT(@strAptNumber, 2) =
'nd' OR RIGHT(@strAptNumber, 2) = 'rd' OR RIGHT(@strAptNumber, 2) =
'th'
BEGIN
SELECT @ConSuiteAptFloor = @strAptNumber + ' ' +
@ConSuiteAptFloor
END
ELSE
BEGIN
SELECT @ConSuiteAptFloor = @ConSuiteAptFloor + ' ' +
@strAptNumber
END
END
END
-- clean up @strAddressFull:
-- remove any commas and trailing spaces:
SELECT @strAddressFull = RTRIM(REPLACE(@strAddressFull, ',', ' '))
-- create the recordsource:
SELECT
x.ID,
x.strTemp,
ZipCode = @ZipCode,
GeoNationID = @GeoNationID,
CityID = @CityID,
Admin1ID = @Admin1ID,
GeoSubregionID = @GeoSubregionID,
ConSuiteAptFloor = @ConSuiteAptFloor,
strAddressFull = @strAddressFull
FROM
#addressTEMP x