Need help with SELECT please.

[LN]

SQL2005/2008.

I have a #stageaddr table and need to compare the address with AddrData
table and return the loannum and correct
adddress fro AddrData table. The trick is #stageAddr has a lot of
garbage data which I import from Excel. Here is
the business rules below and desire results as well. Any help would
greatly appreciate.


IF OBJECT_ID('Tempdb.dbo.#AddrData', 'u') IS NOT NULL
DROP TABLE #AddrData
GO
CREATE TABLE #AddrData
(
LoanNum VARCHAR(10) NULL,
PropertyAddr1 VARCHAR(60) NULL,
PropertyAddr2 VARCHAR(60) NULL
)
go

INSERT #AddrData (LoanNum, PropertyAddr1)
VALUES ('113797', '770 CALIFORNIA AVE');

INSERT #AddrData (LoanNum, PropertyAddr1)
VALUES ('111671', '10017 SKYRIDGE RD');

INSERT #AddrData (LoanNum, PropertyAddr1)
VALUES ('100149', '10229 SW 227 Street');

INSERT #AddrData (LoanNum, PropertyAddr2)
VALUES ('101481', '108 OREGON TRAIL');

INSERT #AddrData (LoanNum, PropertyAddr1)
VALUES ('101613', '14 Selbourne Path');

INSERT #AddrData (LoanNum, PropertyAddr1)
VALUES ('111621', '1766 SW BRISBANE STREET');

go

IF OBJECT_ID('Tempdb.dbo.#StageAddr', 'u') IS NOT NULL
DROP TABLE #StageAddr
GO
CREATE TABLE #StageAddr
(
Addr VARCHAR(60) NULL
)
go

INSERT #StageAddr (Addr)
VALUES ('770 California Avenue , 93065');
INSERT #StageAddr (Addr)
VALUES ('770 california avenue,');

INSERT #StageAddr (Addr)
VALUES ('10017 SKYRIDGE RD,');

INSERT #StageAddr (Addr)
VALUES ('10229 SW 227 STREET , 33190');
INSERT #StageAddr (Addr)
VALUES ('10229 SW 227 STREET,');

INSERT #StageAddr (Addr)
VALUES ('108 oregon trail waxahachie, TX');
INSERT #StageAddr (Addr)
VALUES ('108 oregon trail , waxahachie TX');

INSERT #StageAddr (Addr)
VALUES ('14 SELBOURNE PATH PALM COAST, FL');
INSERT #StageAddr (Addr)
VALUES ('14 SELBOURNE PATH, PALM COAST FL');

INSERT #StageAddr (Addr)
VALUES ('14 SELBOURNE PATH PALM COAST, FL');
INSERT #StageAddr (Addr)
VALUES ('14 SELBOURNE PATH, PALM COAST FL');

INSERT #StageAddr (Addr)
VALUES ('1766 SW BRISBANE STREET,');

INSERT #StageAddr (Addr)
VALUES ('1777 ala moana blvd #938 , 96815,');
INSERT #StageAddr (Addr)
VALUES ('1777 ala moana blvd #938,');
go

-- Rules: Compare Addr from #stageAddr table with Addressproperty1 or
addressproperty2 in #AddrData, If the
Addressproperty1 is null check for addressproperty2 and return the
address alonge with loannum.

SELECT *
FROM #AddrData
--WHERE Address LIKE '10017 SKYRIDGE RD%'
go


SELECT Addr
FROM #StageAddr
go

-- Result want:

LoanNum Addr
---------- --------------------------
113797 770 CALIFORNIA AVE
111671 10017 SKYRIDGE RD
100149 10229 SW 227 Street
101481 108 OREGON TRAIL
101613 14 Selbourne Path
111621 1766 SW BRISBANE STREET

[LN]

[--CELKO--]

Mellisa Data and other companies sell tools just for cleaning up
addresses and putting them in the correct format for the USPS. I
think you might find that they are cheaper than doing this clean up by
hand over and over, year after year.