How to add Percentage of similar compare two string ?

Question

I work on sql server 2019 i have issue i can't get percentage of two string from table

so i will compare between two string columns (PartText,MaskText)

and i will display similar percentage of two column

so as sample 'TR00123907FG','KB00123907FG' two string different only on first two charachters

so difference will be 80 percent

 create table #compareTextPercentage
 (
 PartText varchar(50),
 MaskText varchar(50)
 )
 insert into #compareTextPercentage(PartText,MaskText)
 values
 ('TR00123907FG','KB00123907FG'),
 ('TR00123907FG','TR00123907FG'),
 ('klmhedf43122','50ghlpnkyzhy')

Amit Mohanty · Answer

Try this 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 @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 Execute the above stored procedure and function in you db and call the proc to check matching. SELECT [dbo].[GetPercentageOfTwoStringMatching]('TR00123907FG' ,'KB00123907FG') ref: https://stackoverflow.com/questions/8518695/t-sql-get-percentage-of-character-match-of-2-strings