A SQL Server Table with Non-exact Duplicates

Using the AdventureWorks database, I created my own table called tblCustomers from the Person.Contacts, Person.Address, and Person.AddressType tables. I added everyone in to my table and created some non-exact duplicates by adding records with a Replace statement to make them slightly different (replace Street with St., Road with Rd., etc.). The results are shown in the image below.

Insert into [dbo].[tblCustomer] (AccountNumber,AddrType, CustomerName,addr1 , Addr2, addr3)
select AccountNumber,AddrType, CustomerName, replace (addr1,'Street', 'St.'), Addr2, addr3
from [dbo].[tblCustomer]
where addr1 like '%street%'
order by CustomerName

Create a User Defined Type & Function

Here we create a User Defined Type to put the whole address block in 1 varchar(8000) datatype and then put into a Function that will normalize items within the address to help us extract the non-exact matches. (ex/ Avenue is changed to Ave, Road changed to Rd, etc.).

CREATE TYPE [dbo].[udt_AddressType] FROM [varchar](8000) NULL
GO

create
  Function [dbo].[fnStandardizeAddress] (@cred udt_addressType)
Returns varchar(8000)

Begin

 Declare @rstrAddress udt_AddressType
 Declare @intI int
 Declare @intPos int
 Declare @strAddress varchar(8000)
 Declare @strLetter varchar(1)
 Declare @strResult varchar(8000)

 Set @rstrAddress = @cred
 SET @rstrAddress = Replace(@rstrAddress, '#', 'NO ')
 SET @rstrAddress = Replace(@rstrAddress, '''', '')
 SET @rstrAddress = Replace(@rstrAddress, '&', ' & ')
 SET @rstrAddress = Replace(@rstrAddress, ',', ' , ')
 SET @rstrAddress = Replace(@rstrAddress, ' ', ' ')
 SET @rstrAddress = Replace(@rstrAddress, ' ', ' ')
 SET @rstrAddress = Replace(@rstrAddress, ' ', ' ')
 SET @strResult = ''
 set @intI = 1

while @intI <= Len(@rstrAddress)
 begin
    set @strLetter = substring(@rstrAddress, @intI, 1)

   if (@strLetter=char(38) or @strLetter=char(39) or @strLetter=char(48) or @strLetter=char(49) or
   @strLetter=char(50) or @strLetter=char(51) or @strLetter=char(52) or @strLetter=char(53) or
   @strLetter=char(54) or @strLetter=char(55) or @strLetter=char(56) or @strLetter=char(57) or
   @strLetter=char(65) or @strLetter=char(66) or @strLetter=char(67) or @strLetter=char(68) or
   @strLetter=char(69) or @strLetter=char(70) or @strLetter=char(71) or @strLetter=char(72) or
   @strLetter=char(73) or @strLetter=char(74) or @strLetter=char(75) or @strLetter=char(76) or

   @strLetter=char(77) or @strLetter=char(78) or @strLetter=char(79) or @strLetter=char(80) or
   @strLetter=char(81) or @strLetter=char(82) or @strLetter=char(79) or @strLetter=char(80) or
   @strLetter=char(85) or @strLetter=char(86) or @strLetter=char(87) or @strLetter=char(88) or
   @strLetter=char(89) or @strLetter=char(90) or @strLetter=char(91) or @strLetter=char(92) or
   @strLetter=char(93) or @strLetter=char(94) or @strLetter=char(95) or @strLetter=char(96) or
   @strLetter=char(97) or @strLetter=char(98) or @strLetter=char(99) or @strLetter=char(100) or
   @strLetter=char(101) or @strLetter=char(102) or @strLetter=char(103) or @strLetter=char(104) or
   @strLetter=char(105) or @strLetter=char(106) or @strLetter=char(107) or @strLetter=char(108) or
   @strLetter=char(109) or @strLetter=char(110) or @strLetter=char(111) or @strLetter=char(112) or
   @strLetter=char(113) or @strLetter=char(114) or @strLetter=char(115) or @strLetter=char(116) or
   @strLetter=char(117) or @strLetter=char(118) or @strLetter=char(119) or @strLetter=char(120) or
   @strLetter=char(121) or @strLetter=char(122))
 begin
    set @strResult = @strResult + @strLetter
 end
else
 begin
    set @strResult = @strResult + ' '
  End
 set @intI = @intI + 1
end

 set @strAddress = @strResult
 set @strResult = Replace(@strResult, '''', ' ')
 set @strResult = ' ' + @strResult + ' '
 set @strResult = Replace(@strResult, ' AV ', ' AVE ')
 set @strResult = Replace(@strResult, ' AVENUE ', ' AVE ')
 set @strResult = Replace(@strResult, ' BOULEVARD ', ' BLVD ')
 set @strResult = Replace(@strResult, ' BUILDING ', ' BLDG ')
 set @strResult = Replace(@strResult, ' CORPORATION ', ' CORP ')
 set @strResult = Replace(@strResult, ' COMPANY ', ' CO ')
 set @strResult = Replace(@strResult, ' INCORPORATED ', ' INC ')
 set @strResult = Replace(@strResult, ' COURT ', ' CT ')
 set @strResult = Replace(@strResult, ' CRT ', ' CT ')
 set @strResult = Replace(@strResult, ' DEPARTMENT ', ' DEPT ')
 set @strResult = Replace(@strResult, ' DRIVE ', ' DR ')
 set @strResult = Replace(@strResult, ' EAST ', ' E ')
 set @strResult = Replace(@strResult, ' ESQUIRE ', ' ESQ ')
 set @strResult = Replace(@strResult, ' FLOOR ', ' FL ')
 set @strResult = Replace(@strResult, ' HIGHWAY ', ' HWY ')
 set @strResult = Replace(@strResult, ' INCORPORATED ', ' INC ')
 set @strResult = Replace(@strResult, ' LANE ', ' LN ')
 set @strResult = Replace(@strResult, ' L L C ', ' LLC ')
 set @strResult = Replace(@strResult, ' L L P ', ' LLP ')
 set @strResult = Replace(@strResult, ' L P ', ' LP ')
 set @strResult = Replace(@strResult, ' LANE ', ' LN ')
 set @strResult = Replace(@strResult, ' LIMITED LIABILITY CORPORATION ', ' LLC ')
 set @strResult = Replace(@strResult, ' LIMITED LIABILITY PARTNERSHIP ', ' LLP ')
 set @strResult = Replace(@strResult, ' LIMITED PARTNERSHIP ', ' LP ')
 set @strResult = Replace(@strResult, ' LIMITED ', ' LTD ')
 set @strResult = Replace(@strResult, ' NORTH ', ' N ')
 set @strResult = Replace(@strResult, ' NUMBER ', ' NO ')
 set @strResult = Replace(@strResult, ' PARKWAY ', ' PKWY ')
 set @strResult = Replace(@strResult, ' PLACE ', ' PL ')
 set @strResult = Replace(@strResult, ' P O ', ' PO ')
 set @strResult = Replace(@strResult, ' PO B ', ' PO BOX ')
 set @strResult = Replace(@strResult, ' POB ', ' PO BOX ')
 set @strResult = Replace(@strResult, ' POST OFFICE BOX ', ' PO BOX ')
 set @strResult = Replace(@strResult, ' ROAD ', ' RD ')
 set @strResult = Replace(@strResult, ' STREET ', ' ST ')
 set @strResult = Replace(@strResult, ' SOUTH ', ' S ')
 set @strResult = Replace(@strResult, ' SUITE ', ' STE ')
 set @strResult = Replace(@strResult, ' TRAIL ', ' TR ')
 set @strResult = Replace(@strResult, ' WEST ', ' W ')
 set @strResult = Replace(@strResult, ' ', ' ')
 set @strResult = Replace(@strResult, ' ', ' ')
 set @strResult = Replace(@strResult, ' ', ' ')
 set @strResult = RTrim(LTrim(@strResult))
 set @strResult = Replace(@strResult, ' '' ', '''')

 Return @strResult
End

Create a User Defined Table Type

Here I create the Table Type that we can use on SQL Server. Any table, query, or view can be used to populate this table type with data and then the type can be passed to a Stored Procedure or Function for processing.

CREATE TYPE [dbo].[slTableType] AS TABLE(
 [AcctNo] [varchar](25) NULL,
 [AddrName] [varchar](255) NULL,
 [Addr1] [varchar](255) NULL,
 [Addr2] [varchar](255) NULL,
 [Addr3] [varchar](255) NULL,
 [Addr4] [varchar](255) NULL,
 [Addr5] [varchar](255) NULL
 )

GO

Stored Proc to process Table Type

The stored procedure to check exact and non-exact matches is created. The Table Type is passed in as a Read Only variable and then inserted into a Temp Table with an Identity column and a Dupe bit field column to mark off the duplicates found. A cursor is instantiated and opened and the data is read into our User Defined Function for standardization. If the standardized addresses match, one is marked as duplicate. The final recordset returned by the code is the table without the duplicates.

CREATE PROCEDURE [dbo].[usp_DupeChecking]
( @tbl slTableType READONLY)
 AS
Declare @SQL varchar(7000)
Declare @acctNo_curr varchar(255)
Declare @acctNo_prev varchar(255)
Declare @credAddr_curr udt_AddressType
Declare @credAddr_prev udt_AddressType
Declare @mID varchar(255)
Declare @ID_curr int, @ID_prev int

 select ID=IDENTITY (int, 1, 1),
 *, convert(varchar(25),null) as parentid,
 convert(bit, 0) dupe
 into #tmp
 from @tbl

--dupe checking
 DECLARE dupes CURSOR FOR
 SELECT acctNo,
 isNull(AddrName,'') + ' ' + isNull(Addr1,'') + ' ' + isNull(Addr2,'') + ' ' +
 isNull(Addr3,'') + ' ' + isNull(Addr4,'') + ' ' + isNull(Addr5,''), ID
 FROM #tmp
 order by AddrName, Addr1, Addr2,
 Addr3, Addr4, Addr5

OPEN dupes
 FETCH dupes INTO @acctNo_curr, @credAddr_curr, @ID_curr
 -- start the main processing loop.
 select @credAddr_curr = dbo.fnStandardizeAddress(@credAddr_curr)
 set @credAddr_prev = ''
 set @acctNo_prev = ''
 set @mid = @acctNo_Curr

WHILE @@Fetch_Status = 0
 BEGIN
  select @credAddr_curr = dbo.fnStandardizeAddress(@credAddr_curr)
   if (@credAddr_curr = @credAddr_prev) --same address
    begin
     Update #tmp SET ParentID = @mid, Dupe = 1
     WHERE ID = @ID_curr
    end
   else --different address
    begin
      SET @mid = @acctNo_curr
     Update #tmp SET ParentID = @mid WHERE ID = @ID_curr
    end

 set @credAddr_prev = @credAddr_curr
 set @acctNo_prev = @acctNo_curr
 set @ID_prev = @ID_curr

 FETCH dupes INTO @acctNo_curr, @credAddr_curr, @ID_curr
END
 CLOSE dupes
 DEALLOCATE dupes

 select * from #tmp
 where dupe = 0
 order by addrName, Addr1

 DROP TABLE #tmp
GO

Executing the Code for the Table Type

A quick declaration of the table type and an Insert statement to populate it and then an Execute statement to pass it into the Stored Proc and you get the results without the duplicates.

------- some code script calling out the sp and delivering the table type variable.------
declare @t slTableType

insert into @t (AcctNo, AddrName, Addr1,Addr2,Addr3)
select AccountNumber, CustomerName, addr1, addr2,addr3
from dbo.tblCustomer

execute usp_DupeChecking @tbl=@t