---两个字段顺序相似

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

 

---两个字段简单相似

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