Bark Software: May 2013

在 Programming 的時候, 有很多地方我們需要用到字串相似度的檢驗, 在 PHP 裡有現成的 Function 可以使用, 但是在其他地方就未必.

這裡有一個 JavaScript 的 Implementation. Click here!

在 MS SQL 上, 我根據這個 JavaScript 改了一個版本, 勉強可以使用

CREATE FUNCTION [dbo].[similar_text]
(
   @first   NVARCHAR(100),
   @second NVARCHAR(100),
   @percent BIT = 1
)
RETURNS DECIMAL(10,3)
AS
BEGIN
   DECLARE @ret DECIMAL(10,3);

   DECLARE @s1 NVARCHAR(100);
   DECLARE @s2 NVARCHAR(100);

   DECLARE @pos1 INT;
   DECLARE @pos2 INT;
   DECLARE @max INT;
   DECLARE @fl1 INT;
   DECLARE @fl2 INT;
   DECLARE @p    INT;
   DECLARE @q    INT;
   DECLARE @l    INT;
   DECLARE @sum INT;

   SET @s1 = ISNULL(@first,'');
   SET @s2 = ISNULL(@second,'');

   IF (@s1='') OR (@s2='') BEGIN
       SET @ret = 0.00;
   END
   ELSE BEGIN
       SET @pos1 = 1;
       SET @pos2 = 1;
       SET @max = 1;
       SET @fl1 = len(@s1);
       SET @fl2 = len(@s2);
       SET @p    = 1;

       WHILE (@p<=@fl1) BEGIN
           SET @q = 1;
           WHILE (@q<=@fl2) BEGIN
               SET @l = 1;
               WHILE (@p+@l<=@fl1) AND (@q+@l<=@fl2) AND (SUBSTRING( @s1,@p+@l,1)=SUBSTRING( @s2,@q+@l,1)) BEGIN
                   IF (@l>=@max) BEGIN
                       SET @max = @l;
                       SET @pos1 = @p;
                       SET @pos2 = @q;
                   END;
                   SET @l = @l+1;
               END;
               SET @q = @q+1;
           END;
           SET @p = @p+1;
       END;

       SET @sum = @max;
       IF (@sum>1) BEGIN
           IF (@pos1>=1) AND (@pos2>=1) BEGIN
               SET @sum = @sum + dbo.similar_text(SUBSTRING(@s1,1,@pos2),SUBSTRING(@s2,1,@pos2),0);
           END;

           IF (@pos1+@max<=@fl1) AND (@pos2+@max<=@fl2) BEGIN
               SET @sum = @sum + dbo.similar_text(SUBSTRING(@s1,@pos1+@max,@fl1-@pos1-@max),SUBSTRING(@s2,@pos2+@max,@fl2-@pos2-@max),0);
           END;
       END;

       IF (@percent=0)
           SET @ret = @sum;
       ELSE
           SET @ret = (@sum * 200.00) / (@fl1 + @fl2);
   END;

   RETURN @ret;
END

GO

Bark Software

5/15/2013

Similar Text