none
SQL Fuzzy match (matching names not exact) RRS feed

  • 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 :)

     

    Wednesday, October 19, 2011 8:15 PM

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

    http://www.sqlservercentral.com/articles/Fuzzy+Match/65702/

    • Marked as answer by Borg239 Wednesday, October 19, 2011 8:42 PM
    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

    http://www.sqlservercentral.com/articles/Fuzzy+Match/65702/

    • Marked as answer by Borg239 Wednesday, October 19, 2011 8:42 PM
    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/en-us/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

    Wednesday, October 19, 2011 8:49 PM
    Answerer
  • 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

                    
    Friday, September 2, 2016 11:03 AM
  • It seems first block of code(While loop) is good...second block of code it is always taking the last character...can you explain it what you are doing in second block of code

    Thursday, September 22, 2016 7:14 PM