RE: Record linkage advice required– Long read (YOU HAVE BEEN WARNED!)

ChrisM@Work

SSC Guru

Points: 186127

July 31, 2012 at 3:29 am

#1519588

Here you go mate:

CREATE FUNCTION [dbo].[FuzzyMatch_iTVF2k5]

(

@Reference VARCHAR(100),

@Target VARCHAR(100)

)

RETURNS table

AS

RETURN

SELECT d.Result, MatchRatio = CAST(CASE

WHEN d.Result = 1 THEN 100

WHEN d.Result = 3 THEN DATALENGTH(@Target)*100.00/DATALENGTH(@Reference)

WHEN d.Result = 4 THEN DATALENGTH(@Reference)*100.00/DATALENGTH(@Target)

WHEN d.Result = 5 THEN

(SELECT MatchPC = CAST((100.00*COUNT(*)/(DATALENGTH(@Reference)-2.00)) AS DECIMAL(5,2))

* CASE WHEN DATALENGTH(@Target) > DATALENGTH(@Reference) THEN DATALENGTH(@Reference)*1.00 / DATALENGTH(@Target)*1.00 ELSE 1 END

FROM (

SELECT TOP (ISNULL(DATALENGTH(@Reference),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) a (u)

CROSS JOIN (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) b (t)

) Tally (n)

WHERE CHARINDEX(SUBSTRING(@Reference, Tally.n, 3), @Target) > 0

AND DATALENGTH(SUBSTRING(@Reference, Tally.n, 3)) = 3)

WHEN d.Result = 6 THEN

(SELECT MatchPC = CAST((100.00*COUNT(*)/(DATALENGTH(@Reference)-1.00)) AS DECIMAL(5,2))

* CASE WHEN DATALENGTH(@Target) > DATALENGTH(@Reference) THEN DATALENGTH(@Reference)*1.00 / DATALENGTH(@Target)*1.00 ELSE 1 END

FROM (

SELECT n = t1.n

FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL

SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1 (n)

WHERE t1.n <= DATALENGTH(@Reference)

) Tally

WHERE CHARINDEX(SUBSTRING(@Reference, Tally.n, 2), @Target) > 0

AND DATALENGTH(SUBSTRING(@Reference, Tally.n, 2)) = 2)

ELSE NULL

END AS DECIMAL(5,2))

FROM (

SELECT Result = CASE

WHEN @Reference = @Target THEN 1

WHEN @Reference IS NULL OR @Target IS NULL THEN 2

WHEN @Reference LIKE '%'+@Target+'%' THEN 3

WHEN @Target LIKE '%'+@Reference+'%' THEN 4

WHEN DATALENGTH(@Reference) > 7 AND DATALENGTH(@Target) > 7 THEN 5

WHEN DATALENGTH(@Reference) > 2 AND DATALENGTH(@Target) > 2 THEN 6 -- SOUNDEX may work better

ELSE 7

END

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden