T-SQL obtenir le pourcentage de correspondance de caractère de 2 chaînes
disons que j'ai un ensemble de 2 mots:
Alexandre et Alecsander ou Alexandre et Alegzander
Alexander et Aleaxnder, ou toute autre combinaison. En général, nous parlons de l'homme d'erreur en tapant un mot ou un ensemble de mots.
Ce que je veux réaliser est d'obtenir le pourcentage de correspondance des caractères des 2 chaînes.
Voici ce que j'ai jusqu'à présent:
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 retournera le suivantes:
ch1 ch2
NULL 5
[blank] [blank]
D D
O O
R R
W W
2 réponses
Ok, voici ma solution pour l'instant:
SELECT [dbo].[GetPercentageOfTwoStringMatching]('valentin123456' ,'valnetin123456')
retourne 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
en fin de compte, vous semblez chercher à résoudre la probabilité que deux chaînes soient "floues" l'une par rapport à l'autre.
SQL fournit des fonctions intégrées efficaces et optimisées qui feront cela pour vous, et probablement avec de meilleures performances que ce que vous avez écrit. Les deux fonctions sont SOUNDEX et différence.
alors qu'aucun des deux ne résout exactement ce que vous avez demandé - c'est à dire qu'ils ne retournez pas un match de pourcentage - je crois qu'ils résolvent ce que vous essayez finalement d'atteindre.
SOUNDEX renvoie un code de 4 caractères qui est la première lettre du mot plus un code de 3 chiffres qui représente le motif sonore du mot. Considérons ce qui suit:
SELECT SOUNDEX('Alexander')
SELECT SOUNDEX('Alegzander')
SELECT SOUNDEX('Owleksanndurr')
SELECT SOUNDEX('Ulikkksonnnderrr')
SELECT SOUNDEX('Jones')
/* Results:
A425
A425
O425
U425
J520
*/
ce que vous remarquerez, c'est que le numéro à trois chiffres 425 est le même pour tous ceux qui se ressemblent à peu près. Donc vous pouvez facilement les apparier et dire " Vous avez tapé 'Owleksanndurr', vous vouliez dire "Alexandre"?"
en plus, il y a le DIFFERENCE, qui compare la fonction SOUNDEX divergence entre deux chaînes et lui donne une partition.
SELECT DIFFERENCE( 'Alexander','Alexsander')
SELECT DIFFERENCE( 'Alexander','Owleksanndurr')
SELECT DIFFERENCE( 'Alexander', 'Jones')
SELECT DIFFERENCE( 'Alexander','ekdfgaskfalsdfkljasdfl;jl;asdj;a')
/* Results:
4
3
1
1
*/
comme vous pouvez le voir, plus le score est bas (entre 0 et 4), plus les chaînes sont susceptibles de correspondre.
L'avantage de SOUNDEXDIFFERENCE c'est que si vous avez vraiment besoin de faire de fréquentes correspondance floue, vous pouvez stocker et indexer le SOUNDEX données dans une colonne séparée (indexable) , alors que les DIFFERENCE ne peut calculer que le SOUNDEX au moment de la comparaison.