# Getting percentage of character match of 2 strings – MSSQL – SQL Server / Alternative to SOUNDEX

Let’s say I have a set of 2 words:

Alexander and Alecsander OR Alexander and Alegzander

Alexander and Aleaxnder, or any other combination. In general we are talking about human error in typing of a word or a set of words.

What I want to achieve is to get the percentage of matching of the characters of the 2 strings.

Ok, here is my solution so far:

```SELECT  [dbo].[GetPercentageOfTwoStringMatching]('valentin123456'  ,'valnetin123456')
```

returns 86%

```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 the result of the function
RETURN @percentageOfGoodCharacters

END

-- =============================================
-- Create date: 2011.12.14
-- Description: http://blog.sendreallybigfiles.com/2009/06/improved-t-sql-levenshtein-distance.html
-- =============================================

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
```

The solution uses the Levenstein algorithm:

The Levenshtein distance is a string metric for measuring the difference between two sequences. Informally, the Levenshtein distance between two words is the minimum number of single-character edits (insertions, deletions or substitutions) required to change one word into the other. It is named after the Soviet mathematician Vladimir Levenshtein, who considered this distance in 1965.