Need help with SELECT please. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with SELECT please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-11-19 : 11:10:42

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-11-19 : 11:57:37

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

SELECT distinct a.LoanNum,coalesce(a.PropertyAddr1,PropertyAddr2) as Addr
FROM #AddrData a
Inner Join
#StageAddr b
on b.Addr like '%' + rtrim(a.PropertyAddr1) + '%'
or b.Addr like '%' + rtrim(a.PropertyAddr2) + '%'
--WHERE Address LIKE '10017 SKYRIDGE RD%'
go

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources