Answered by:
SQL Fuzzy match (matching names not exact)
Question

Hi,
assuming i have the right naming, what i am trying to write is a function or storedprocedure to compare names and find out if they are the same person.
i think its called fuzzy matching
ie: table a has 1 row 1 column, table b has 1 row 1 column
table a , column 1 [ santa clause ]
table b , column 1 [ sanata claause ]
somehow it needs to know its the same person :)
Answers

Hi,
assuming i have the right naming, what i am trying to write is a function or storedprocedure to compare names and find out if they are the same person.
i think its called fuzzy matching
ie: table a has 1 row 1 column, table b has 1 row 1 column
table a , column 1 [ santa clause ]
table b , column 1 [ sanata claause ]
somehow it needs to know its the same person :)
nvm find the perfect solution
 Marked as answer by Borg239 Wednesday, October 19, 2011 8:42 PM
All replies

Hi,
assuming i have the right naming, what i am trying to write is a function or storedprocedure to compare names and find out if they are the same person.
i think its called fuzzy matching
ie: table a has 1 row 1 column, table b has 1 row 1 column
table a , column 1 [ santa clause ]
table b , column 1 [ sanata claause ]
somehow it needs to know its the same person :)
nvm find the perfect solution
 Marked as answer by Borg239 Wednesday, October 19, 2011 8:42 PM

Hi Borg !
You might wanna look into
SOUNDEX
function. it will make your work lot easier.http://msdn.microsoft.com/enus/library/aa259235(v=sql.80).aspx
Please let me know if this doesn’t work for you. Hope I have answered you correctly.
Thanks, Hasham
 Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Saturday, November 3, 2012 1:54 PM

Create FUNCTION [dbo].[f_MatchData]
(
@Column1 varchar(max)
,@Column2 varchar(max)
)

 Developed by Martin Fourie  20160902

RETURNS decimal(18,2)
AS
BEGIN
declare @Max int, @Left int, @Right int, @Check int, @Result decimal(18,2)
set @Check = 0
set @Max = case when len(@Column1) > len(@Column2) then len(@Column1) else len(@Column2) end
set @Left = 1
set @Right = 1
while @Max > 0
begin
declare @T1 varchar(1), @T2 varchar(1)
set @T1 = (right(left(@Column1,@Left),@Right))
set @T2 = (right(left(@Column2,@Left),@Right))
set @Check = @Check + (case when @T1 = @T2 then 1 else 0 end)
set @Left = @Left + 1
set @Max = @Max  1
end



set @Max = case when len(@Column1) > len(@Column2) then len(@Column1) else len(@Column2) end
set @Left = 1
set @Right = 1
while @Max > 0
begin
declare @T3 varchar(1), @T4 varchar(1)
set @T3 = (left(right(@Column1,@Right),@Left))
set @T4 = (left(right(@Column2,@Right),@Left))
set @Check = @Check + (case when @T3 = @T4 then 1 else 0 end)
set @Left = @Right + 1
set @Max = @Max  1
end
set @Max = case when len(@Column1) > len(@Column2) then len(@Column1) else len(@Column2) end
set @Result = cast((@Check * 1.00)/(@Max * 2.00) * 100 as decimal(18,2))
RETURN @Result
END
