T-SQL 获取2个字符串的字符匹配百分比 [英] T-SQL Get percentage of character match of 2 strings
本文介绍了T-SQL 获取2个字符串的字符匹配百分比的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有一组 2 个词:
Let's say I have a set of 2 words:
亚历山大和亚历山德 OR 亚历山大和亚历山德
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.
我想要实现的是获取2个字符串的字符匹配的百分比.
What I want to achieve is to get the percentage of matching of the characters of the 2 strings.
这是我目前所拥有的:
DECLARE @table1 TABLE
(
nr INT
, ch CHAR
)
DECLARE @table2 TABLE
(
nr INT
, ch CHAR
)
INSERT INTO @table1
SELECT nr,ch FROM [dbo].[SplitStringIntoCharacters] ('WORD w') --> return a table of characters(spaces included)
INSERT INTO @table2
SELECT nr,ch FROM [dbo].[SplitStringIntoCharacters] ('WORD 5')
DECLARE @resultsTable TABLE
(
ch1 CHAR
, ch2 CHAR
)
INSERT INTO @resultsTable
SELECT DISTINCt t1.ch ch1, t2.ch ch2 FROM @table1 t1
FULL JOIN @table2 t2 ON t1.ch = t2.ch --> returns both matches and missmatches
SELECT * FROM @resultsTable
DECLARE @nrOfMathches INT, @nrOfMismatches INT, @nrOfRowsInResultsTable INT
SELECT @nrOfMathches = COUNT(1) FROM @resultsTable WHERE ch1 IS NOT NULL AND ch2 IS NOT NULL
SELECT @nrOfMismatches = COUNT(1) FROM @resultsTable WHERE ch1 IS NULL OR ch2 IS NULL
SELECT @nrOfRowsInResultsTable = COUNT(1) FROM @resultsTable
SELECT @nrOfMathches * 100 / @nrOfRowsInResultsTable
SELECT * FROM @resultsTable
将返回以下内容:
ch1 ch2
NULL 5
[blank] [blank]
D D
O O
R R
W W
推荐答案
好的,这是我目前的解决方案:
Ok, here is my solution so far:
SELECT [dbo].[GetPercentageOfTwoStringMatching]('valentin123456' ,'valnetin123456')
回报率 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
这篇关于T-SQL 获取2个字符串的字符匹配百分比的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文