Here are two functions - the first is a very close approximation of the Levenshtein Distance Algorithm, the second is a stripped-down slicked-back twin carburetor version which in practice produces very similar results.
CREATE FUNCTION [dbo].[FuzzyMatch_iTVF2k5]
(@Reference VARCHAR(100) = NULL,
@Target VARCHAR(100) = NULL)
RETURNS table WITH SCHEMABINDING
AS
-- Chris Morris 2012
-- Fuzzy-matching using shifted character positions
-- See also http://research.microsoft.com/pubs/75996/bm_sigmod03.pdf
RETURN
SELECT
d.Method,
MatchRatio = CAST(CASE
WHEN d.Method = 1 THEN 100
WHEN d.Method = 3 THEN LenTarget*100.00/LenReference
WHEN d.Method = 4 THEN LenReference*100.00/LenTarget
WHEN d.Method = 5 THEN
(
SELECT
MatchPC = (100.00 * ISNULL(NULLIF(SUM(
CASE WHEN Tally.n < PosInTarget THEN Tally.n/PosInTarget ELSE PosInTarget/Tally.n END
),0)+2.00,0) / LenReference)
* CASE WHEN LenTarget > LenReference THEN LenReference/LenTarget ELSE 1.00 END
FROM ( -- Tally
SELECT TOP (CAST(LenReference AS INT)-2) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n)) a,
(SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n)) b
) Tally
CROSS APPLY (SELECT PosInTarget = CHARINDEX(SUBSTRING(@Reference, Tally.n, 3), @Target)) x
)
WHEN d.Method = 6 THEN
(
SELECT
MatchPC = (100.00 * ISNULL(NULLIF(SUM(
CASE WHEN Tally.n < PosInTarget THEN Tally.n/PosInTarget ELSE PosInTarget/Tally.n END
),0)+1.00,0) / LenReference)
* CASE WHEN LenTarget > LenReference THEN LenReference/LenTarget ELSE 1.00 END
FROM ( -- Tally
SELECT TOP (CAST(LenReference AS INT)-1) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n)
) Tally
CROSS APPLY (SELECT PosInTarget = CAST(CHARINDEX(SUBSTRING(@Reference, Tally.n, 2), @Target) AS DECIMAL(5,2))) x
)
ELSE NULL
END AS DECIMAL(5,2))
FROM (
SELECT Method = 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
ELSE 7
END,
LenTarget = CAST(DATALENGTH(@Target) AS DECIMAL(5,2)),
LenReference = CAST(DATALENGTH(@Reference) AS DECIMAL(5,2))
) d
-----------------------------------------------------------------
CREATE FUNCTION [dbo].[FuzzyMatch_iTVFV2]
(@Reference VARCHAR(100) = NULL,
@Target VARCHAR(100) = NULL)
RETURNS table WITH SCHEMABINDING
AS
-- Chris Morris 2012
-- Fuzzy-matching
-- See also http://research.microsoft.com/pubs/75996/bm_sigmod03.pdf
RETURN
WITH Tally AS (
SELECT TOP (DATALENGTH(@Reference)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n)) a,
(SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n)) b
)
SELECT MatchRatio = SUM(Matches)/DATALENGTH(@Reference)
FROM Tally
CROSS APPLY (SELECT TestLetter = SUBSTRING(@Reference, Tally.n, 1)) x
CROSS APPLY (
SELECT -- start search one character position *before* n to catch switched letters
PosInReference = CAST(CHARINDEX(TestLetter, @Reference, n-1) AS DECIMAL(5,2)),
PosInTarget = CAST(CHARINDEX(TestLetter, @Target, n-1) AS DECIMAL(5,2))
) z
CROSS APPLY (
SELECT Matches = CASE WHEN PosInReference > PosInTarget
THEN PosInTarget/PosInReference ELSE PosInReference/PosInTarget END
) m
--------------------------------------------------------------------------------------------------------------------
Here's an example of usage:
;WITH
SampleData1 AS (SELECT * FROM (VALUES ('Victor Proteus'), ('Victor Protus'), ('Victor ProteEus')) d (String1)),
SampleData2 AS (SELECT * FROM (VALUES ('Victor Proteus'), ('Victor Protes'), ('Proteus Victor')) d (String2))
SELECT *
FROM SampleData1 d1
CROSS JOIN SampleData2 d2
CROSS APPLY dbo.FuzzyMatch_iTVFV2 (d1.String1, d2.String2) m
WHERE d1.String1 > d2.String2
AND m.MatchRatio > 0.4
ORDER BY d1.String1, d2.String2