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')  

expected result

Answer 1

Hi @ahmed salah ,

There is a built-in SQL Server function: DIFFERENCE

DIFFERENCE compares two different SOUNDEX values, and returns an integer value. This value measures the degree that the SOUNDEX values match, on a scale of 0 to 4. A value of 0 indicates weak or no similarity between the SOUNDEX values; 4 indicates strongly similar.

Check it out a solution below.
I used 25 as a coefficient. You can try any other suitable number or even a formula to correlate for your needs.

SQL

DECLARE @tbl table (ID INT IDENTITY PRIMARY KEY,  PartText varchar(50),  MaskText varchar(50));  
INSERT INTO @tbl (PartText,MaskText) VALUES  
 ('TR00123907FG','KB00123907FG'),  
 ('TR00123907FG','TR00123907FG'),  
 ('klmhedf43122','50ghlpnkyzhy');  
  
SELECT *   
 , Diff = DIFFERENCE(PartText, MaskText) * 25  
FROM @tbl;  

Output

+----+--------------+--------------+------+  
| ID |   PartText   |   MaskText   | Diff |  
+----+--------------+--------------+------+  
|  1 | TR00123907FG | KB00123907FG |   50 |  
|  2 | TR00123907FG | TR00123907FG |  100 |  
|  3 | klmhedf43122 | 50ghlpnkyzhy |    0 |  
+----+--------------+--------------+------+  

Answer 2

Hi @ahmed salah ,

Sorry.
I used it for the first time and listed it in the wrong place.
How about this query?

SQL

DECLARE @tbl table (PartText varchar(50),  MaskText varchar(50))  
INSERT INTO @tbl (PartText,MaskText) VALUES  
('TR00123907FG','KB00123907FG'),  
('TR00123907FG','TR00123907FG'),  
('klmhedf43122','50ghlpnkyzhy');  
  
with num as (  
	select 1 + n1.value * 10 + n2.value as n   
	from   
	string_split('0,1,2,3,4,5,6,7,8,9',',') as n1  
,string_split('0,1,2,3,4,5,6,7,8,9',',') as n2  
),temp as (  
	select distinct  
	PartText  
	,MaskText  
	,TextCnt  
	,sum(iif(SUBSTRING(PartText,n,1) =SUBSTRING(MaskText,n,1) ,1,0) ) over(partition by IdText) as CompCnt  
	from @tbl as tbl   
	cross apply(  
	select n,max(n) over() TextCnt,concat(PartText,MaskText)IdText from num where n <= len(iif(PartText>MaskText,PartText,MaskText))  
	) as n  
)  
  
select   
PartText  
,MaskText  
,round(cast(CompCnt as float) / TextCnt * 100,-1,1) as Precentage  
from temp  
order by 1 desc;  

output

 +--------------+--------------+---------+  
 |   PartText   |   MaskText   | PartText|  
 +--------------+--------------+---------+  
 | klmhedf43122 | 50ghlpnkyzhy |       0 |  
 | TR00123907FG | KB00123907FG |      80 |  
 | TR00123907FG | TR00123907FG |     100 |  
 +--------------+--------------+---------+  

Answer 3

Hi @ahmed salah ,

If you want to get the percentage similarity of 2 strings, you could have a look at this similar thread that provides a method in T-SQL.

I've tried it and it works pretty well.

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  
  
  
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   
  
  SELECT  *,[dbo].[GetPercentageOfTwoStringMatching](MaskText  ,'TR00123907FG') as[percent] from #compareTextPercentage  

output:

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".