Fix Customer Name

FixCustomerName.sql

DROP FUNCTION IF EXISTS [dbo].[LEVENSHTEIN]
GO

CREATE FUNCTION [dbo].[LEVENSHTEIN](@left  VARCHAR(100),
                                    @right VARCHAR(100))
RETURNS INT
AS
  BEGIN
      DECLARE @difference    INT,
              @lenRight      INT,
              @lenLeft       INT,
              @leftIndex     INT,
              @rightIndex    INT,
              @left_char     CHAR(1),
              @right_char    CHAR(1),
              @compareLength INT

      SET @lenLeft = LEN(@left)
      SET @lenRight = LEN(@right)
      SET @difference = 0

      IF @lenLeft = 0
        BEGIN
            SET @difference = @lenRight

            GOTO done
        END

      IF @lenRight = 0
        BEGIN
            SET @difference = @lenLeft

            GOTO done
        END

      GOTO comparison

      COMPARISON:

      IF ( @lenLeft >= @lenRight )
        SET @compareLength = @lenLeft
      ELSE
        SET @compareLength = @lenRight

      SET @rightIndex = 1
      SET @leftIndex = 1

      WHILE @leftIndex <= @compareLength
        BEGIN
            SET @left_char = substring(@left, @leftIndex, 1)
            SET @right_char = substring(@right, @rightIndex, 1)

            IF @left_char <> @right_char
              BEGIN -- Would an insertion make them re-align?
                  IF( @left_char = substring(@right, @rightIndex + 1, 1) )
                    SET @rightIndex = @rightIndex + 1
                  -- Would an deletion make them re-align?
                  ELSE IF( substring(@left, @leftIndex + 1, 1) = @right_char )
                    SET @leftIndex = @leftIndex + 1

                  SET @difference = @difference + 1
              END

            SET @leftIndex = @leftIndex + 1
            SET @rightIndex = @rightIndex + 1
        END

      GOTO done

      DONE:

      RETURN @difference
  END 
GO

DROP FUNCTION IF EXISTS [dbo].[GetPercentageOfTwoStringMatching]
GO

CREATE FUNCTION [dbo].[GetPercentageOfTwoStringMatching]
(
    @string1 NVARCHAR(100),
    @string2 NVARCHAR(100)
)
RETURNS INT
AS
BEGIN
    DECLARE @levenShteinNumber INT
    DECLARE @string1Length INT = LEN(@string1), @string2Length INT = LEN(@string2)
    DECLARE @maxLengthNumber INT = CASE WHEN @string1Length > @string2Length THEN @string1Length ELSE @string2Length END
    SELECT @levenShteinNumber = [dbo].[LEVENSHTEIN] (   @string1  ,@string2)
    DECLARE @percentageOfBadCharacters INT = @levenShteinNumber * 100 / @maxLengthNumber
    DECLARE @percentageOfGoodCharacters INT = 100 - @percentageOfBadCharacters
    RETURN @percentageOfGoodCharacters
END
GO

DROP TABLE IF EXISTS [TBank].[dbo].[_TempCustomers] 
GO

SELECT *
INTO [TBank].[dbo].[_TempCustomers] 
FROM 
(
	SELECT
	      1000000 + (ROW_NUMBER() OVER (ORDER BY c.MELLI_CARD)) as Id,
		  c.Owner, 
		  c.MELLI_CARD,
		  c.FName,
		  c.LName,		  
		  ROW_NUMBER() OVER (PARTITION BY c.MELLI_CARD ORDER BY c.FName+c.LName) as RowNo,
		  RANK() OVER (PARTITION BY c.MELLI_CARD ORDER BY c.FName+c.LName) AS RankNo,
		  ISNULL([dbo].[GetPercentageOfTwoStringMatching](c.FName+c.LName, LAG(c.FName+c.LName, 1) OVER (PARTITION BY c.MELLI_CARD ORDER BY c.FName+c.LName)), 100) As DiffNo,
		  0 AS Checked
	FROM [TBank].[dbo].[Customer] c
	WHERE [dbo].[IsNationalCode] (c.MELLI_CARD) = 1
) t
ORDER BY t.MELLI_CARD, t.Owner

SELECT *
FROM [TBank].[dbo].[_TempCustomers] 
WHERE [MELLI_CARD] IN 
(
	SELECT  [MELLI_CARD]
	FROM [TBank].[dbo].[_TempCustomers]
	GROUP BY [MELLI_CARD]
	HAVING AVG(RankNo) > 1 AND AVG(DiffNo) < 90
)
ORDER BY MELLI_CARD, RankNo DESC