FuzzyPercent.sql

CREATE FUNCTION dbo.FuzzyPercent
(
    @String1 NVARCHAR(MAX),
    @String2 NVARCHAR(MAX),
    @Algorithm INT = 3,
    @Normalised BIT = 0
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @intScore INT, @intTotScore INT;

    -- If strings haven't been normalised, normalise them
    IF @Normalised = 0
    BEGIN
        SET @String1 = LOWER(LTRIM(RTRIM(@String1)));
        SET @String2 = LOWER(LTRIM(RTRIM(@String2)));
    END

    -- Give 100% match if strings exactly equal
    IF @String1 = @String2
    BEGIN
        RETURN 1;
    END

    SET @intTotScore = 0; -- Initialise total possible score
    SET @intScore = 0; -- Initialise current score

    -- If Algorithm = 1 or 3, Search for single characters
    IF (@Algorithm & 1) <> 0
    BEGIN
        -- FuzzyAlg1
        EXEC dbo.FuzzyAlg1 @String1, @String2, @intScore OUTPUT, @intTotScore OUTPUT;
        IF LEN(@String1) < LEN(@String2)
        BEGIN
            -- FuzzyAlg1
            EXEC dbo.FuzzyAlg1 @String2, @String1, @intScore OUTPUT, @intTotScore OUTPUT;
        END
    END

    -- If Algorithm = 2 or 3, Search for pairs, triplets etc.
    IF (@Algorithm & 2) <> 0
    BEGIN
        -- FuzzyAlg2
        EXEC dbo.FuzzyAlg2 @String1, @String2, @intScore OUTPUT, @intTotScore OUTPUT;
        IF LEN(@String1) < LEN(@String2)
        BEGIN
            -- FuzzyAlg2
            EXEC dbo.FuzzyAlg2 @String2, @String1, @intScore OUTPUT, @intTotScore OUTPUT;
        END
    END

    RETURN CASE WHEN @intTotScore > 0 THEN @intScore / @intTotScore ELSE 0 END;
END;


CREATE FUNCTION dbo.FuzzyAlg1
(
    @String1 NVARCHAR(MAX),
    @String2 NVARCHAR(MAX),
    @Score INT OUTPUT,
    @TotScore INT OUTPUT
)
AS
BEGIN
    DECLARE @intLen1 INT, @intPos INT, @intPtr INT, @intStartPos INT;

    SET @intLen1 = LEN(@String1);
    SET @TotScore = @TotScore + @intLen1; -- Update total possible score
    SET @Score = 0;

    SET @intPos = 0;

    FOR @intPtr = 1 TO @intLen1
    BEGIN
        SET @intStartPos = @intPos + 1;
        SET @intPos = CHARINDEX(SUBSTRING(@String1, @intPtr, 1), @String2, @intStartPos);

        IF @intPos > 0
        BEGIN
            IF @intPos > @intStartPos + 3
            BEGIN
                SET @intPos = @intStartPos;
            END
            ELSE
            BEGIN
                SET @Score = @Score + 1; -- Update current score
            END
        END
        ELSE
        BEGIN
            SET @intPos = @intStartPos;
        END
    END
END;


CREATE FUNCTION dbo.FuzzyAlg2
(
    @String1 NVARCHAR(MAX),
    @String2 NVARCHAR(MAX),
    @Score INT OUTPUT,
    @TotScore INT OUTPUT
)
AS
BEGIN
    DECLARE @intCurLen INT, @intLen1 INT, @intTo INT, @intPtr INT, @intPos INT;
    DECLARE @strWork NVARCHAR(MAX);

    SET @intLen1 = LEN(@String1);
    SET @Score = 0;
    SET @TotScore = 0;

    FOR @intCurLen = 2 TO @intLen1
    BEGIN
        SET @strWork = @String2; -- Get a copy of String2
        SET @intTo = @intLen1 - @intCurLen + 1;
        SET @TotScore = @TotScore + CAST(@intLen1 / @intCurLen AS INT); -- Update total possible score

        FOR @intPtr = 1 TO @intTo STEP @intCurLen
        BEGIN
            SET @intPos = CHARINDEX(SUBSTRING(@String1, @intPtr, @intCurLen), @strWork);

            IF @intPos > 0
            BEGIN
                SET @strWork = STUFF(@strWork, @intPos, @intCurLen, REPLICATE(NCHAR(0), @intCurLen)); -- Corrupt found string
                SET @Score = @Score + 1; -- Update current score
            END
        END
    END
END;