SQLsever 两个字段相似程度算法 （总结）

 1.两个字段简单相似

 

---两个字段简单相似 CREATE FUNCTION DBO.FN_Resemble ( @Cloumna NVARCHAR ( MAX ), @Cloumnb NVARCHAR ( MAX ) ) RETURNS FLOAT AS BEGIN DECLARE @num FLOAT , @len int SET @Cloumna = ISNULL ( @Cloumna , 0 ) SET @Cloumnb = ISNULL ( @Cloumnb , 0 ) SET @len = 1 SET @num = 0 WHILE ( LEN ( @Cloumna ) <> 0 AND LEN ( @CloumnB ) <> 0 ) BEGIN WHILE ( @len <= LEN ( @Cloumna )) BEGIN DECLARE @a NVARCHAR ( 4 ) SET @a = '' SET @a = SUBSTRING ( @Cloumna , @len , 1 ) IF ( CHARINDEX ( @a , @CloumnB ) > 0 ) BEGIN SET @num = @num + 1 END SET @len = @len + 1 END SET @num = @num * 1.0 / LEN ( @Cloumna ) BREAK END RETURN @num END ----测试代码 SELECT DBO.FN_Resemble( 'ABDC321G' , 'ABDC123G' )

 

2.两个字段顺序相似

---两个字段顺序相似 CREATE FUNCTION DBO.FN_Resemble_order ( @Cloumna NVARCHAR ( MAX ), @Cloumnb NVARCHAR ( MAX ) ) RETURNS FLOAT AS BEGIN DECLARE @num FLOAT , @len int SET @Cloumna = ISNULL ( @Cloumna , 0 ) SET @Cloumnb = ISNULL ( @Cloumnb , 0 ) SET @len = 1 SET @num = 0 WHILE ( LEN ( @Cloumna ) <> 0 AND LEN ( @CloumnB ) <> 0 ) BEGIN DECLARE @a NVARCHAR ( 4 ) DECLARE @b NVARCHAR ( 4 ) IF ( LEN ( @Cloumna ) >= LEN ( @CloumnB )) BEGIN WHILE ( @len <= LEN ( @CloumnB )) BEGIN SET @a = '' SET @a = SUBSTRING ( @Cloumna , @len , 1 ) SET @b = '' SET @b = SUBSTRING ( @CloumnB , @len , 1 ) IF ( @a = @b ) BEGIN SET @num = @num + 1 END ELSE BEGIN break END SET @len = @len + 1 END END ELSE IF ( LEN ( @Cloumna ) < LEN ( @CloumnB )) BEGIN WHILE ( @len <= LEN ( @Cloumna )) BEGIN SET @a = '' SET @a = SUBSTRING ( @Cloumna , @len , 1 ) SET @b = '' SET @b = SUBSTRING ( @CloumnB , @len , 1 ) IF ( @a = @b ) BEGIN SET @num = @num + 1 END ELSE BEGIN break END SET @len = @len + 1 END END SET @num = @num * 1.0 / LEN ( @Cloumna ) BREAK END RETURN @num END go ----测试代码 SELECT DBO.FN_Resemble_order( 'ABDC456G' , 'ABDC123G' )

 

 

3.两个字段一对一相似

 

---两个字段一对一相似 CREATE FUNCTION DBO.FN_Resemble_onebyone ( @Cloumna NVARCHAR ( MAX ), @Cloumnb NVARCHAR ( MAX ) ) RETURNS FLOAT AS BEGIN DECLARE @num FLOAT , @len int SET @Cloumna = ISNULL ( @Cloumna , 0 ) SET @Cloumnb = ISNULL ( @Cloumnb , 0 ) SET @len = 1 SET @num = 0 WHILE ( LEN ( @Cloumna ) <> 0 AND LEN ( @CloumnB ) <> 0 ) BEGIN DECLARE @a NVARCHAR ( 4 ) DECLARE @b NVARCHAR ( 4 ) IF ( LEN ( @Cloumna ) >= LEN ( @CloumnB )) BEGIN WHILE ( @len <= LEN ( @CloumnB )) BEGIN SET @a = '' SET @a = SUBSTRING ( @Cloumna , @len , 1 ) SET @b = '' SET @b = SUBSTRING ( @CloumnB , @len , 1 ) IF ( @a = @b ) BEGIN SET @num = @num + 1 END SET @len = @len + 1 END END ELSE IF ( LEN ( @Cloumna ) < LEN ( @CloumnB )) BEGIN WHILE ( @len <= LEN ( @Cloumna )) BEGIN SET @a = '' SET @a = SUBSTRING ( @Cloumna , @len , 1 ) SET @b = '' SET @b = SUBSTRING ( @CloumnB , @len , 1 ) IF ( @a = @b ) BEGIN SET @num = @num + 1 END SET @len = @len + 1 END END SET @num = @num * 1.0 / LEN ( @Cloumna ) BREAK END RETURN @num END ----测试代码 SELECT DBO.FN_Resemble_onebyone( 'ABDC456G' , 'ABDC123G' )

两个字段相似

IF object_id('F_strcompare') IS NOT NULL

BEGIN

PRINT 'Dropping function F_strcompare'

DROP FUNCTION F_strcompare

IF @@ERROR = 0 PRINT 'Function F_strcompare dropped '

END

go

CREATE FUNCTION F_strcompare(@str1 VARCHAR(8000),

@str2 VARCHAR(8000))

RETURNS VARCHAR(10)

AS

BEGIN

DECLARE @i INT,

@j INT,

@k INT,

@ret VARCHAR(10)

SELECT @i = MAX(strlen),

@j = MIN(strlen),

@k = 0

FROM (SELECT strlen=Len(@str1)

UNION

SELECT strlen=Len(@str2)) t

IF( @j = 0 )

RETURN '0%'

WHILE @j > 0

BEGIN

IF Substring(@str1, @j, 1) = Substring(@str2, @j, 1)

SET @k=@k + 1

SET @j=@j - 1

END

SET @ret = Rtrim(CAST(@k * 100.0 / @i AS NUMERIC(3, 0))) + '%'

RETURN @ret

END

GO