本文共 2510 字，大约阅读时间需要 8 分钟。

/************************************************************ * Time: 2020-04-10  * Author:JackieZheng * Function:Compare character string * Return:float value ************************************************************/USE [DB]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[FN_StrCompare](	@Stra               NVARCHAR(MAX),	--字符串a	@Strb               NVARCHAR(MAX),	--字符串b	@PosWeight          FLOAT,		--比对时位置权重0-1.0,默认0.5	@ContainsAsTrue     BIT,		--包含是否视为相同,默认0	@IgnoreNul          BIT			--忽略空字符,默认 1)RETURNS FLOATASBEGIN	DECLARE @num          FLOAT,	        @len          INT,	        @StrL         NVARCHAR(MAX),	        @StrS         NVARCHAR(MAX),	        @position     FLOAT		SET @Stra = ISNULL(@Stra, 0)	SET @Strb = ISNULL(@Strb, 0)	SET @PosWeight = ISNULL(@PosWeight, 0.5)	SET @ContainsAsTrue = ISNULL(@ContainsAsTrue, 0)	SET @IgnoreNul = ISNULL(@IgnoreNul, 1)			IF  (@IgnoreNul=1)	BEGIN         SET @Stra =  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Stra,' ',''), CHAR(10), ''), CHAR(13), ''), CHAR(9), ''), '　', '')         SET @Strb =　REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Strb,' ',''), CHAR(10), ''), CHAR(13), ''), CHAR(9), ''), '　', '')	END			IF (LEN(@Stra) > LEN(@Strb))	BEGIN	    SET @StrL = @Stra	    	    SET @StrS = @Strb	END	ELSE	BEGIN	    SET @StrL = @Strb	    SET @StrS = @Stra	END 					IF (@ContainsAsTrue = 1 AND CHARINDEX(@StrS, @StrL) > 0)	BEGIN	    RETURN 1	END			SET @len = 1	SET @num = 0	SET @position = 0	WHILE (LEN(@StrL) <> 0 AND LEN(@StrS) <> 0)	BEGIN	    WHILE (@len <= LEN(@StrS))	    BEGIN	        DECLARE @a NVARCHAR(4)	        SET @a = ''	        SET @a = SUBSTRING(@StrS, @len, 1)	        IF (CHARINDEX(@a, @StrL) > 0)	        BEGIN	            SET @num = @num + 1	            DECLARE @maxIndex INT	            SET @maxIndex = @len 	            IF (CHARINDEX(@a, @StrL) > @maxIndex)	            BEGIN	                SET @maxIndex = CHARINDEX(@a, @StrL)	            END	            	            SET @position = @position + (1 -ABS(@len -CHARINDEX(@a, @StrL, @len)) * 1.0 / @maxIndex)	        END	        	        SET @len = @len + 1	    END	    SET @num = (@num * (1 -@PosWeight) + @position * @PosWeight) / LEN(@StrL)	    BREAK	END		RETURN @numENDGO----测试代码SELECT DBO.FN_StrCompare('中国人', '中华人民共和国', NULL, 0, NULL); --0.377551020408214SELECT DBO.FN_StrCompare('1234', '4321', 0.01, 1, NULL); --0.992291666666667SELECT DBO.FN_StrCompare('36cfe88476f046198c1a99f9f7f3a1e5', '00015e9443434a178df1a6dc9d787fa3', 0.99, 0, NULL);--0.604230480461777

转载地址：http://vskbb.baihongyu.com/