SQLServer自訂函數——fnCompare

/*************************************************************
** File: fnCompare.sql
** Name: fnCompare
** Description: Compare two strings,return the alike degree with the percentage.
  比較兩個字串，返回相似性的百分比。
** Return values: @result 
** Parameters: @chvA,@chvB
** Author: 何毅翔(MyGodness)
** Date: 2005-1-12
** Use Method: select dbo.fnCompare('abcdefg','difdewg')
*************************************************************/

Create Function fnCompare(@chvA varchar(8000),@chvB varchar(8000))
returns varchar(20)
As
Begin
 declare @i int,@j int,@same decimal(10,2),@result varchar(20)
 select @same=0
 If len(@chvA)>=len(@chvB)
  select @i=len(@chvA),@j=len(@chvB)
 Else
  select @i=len(@chvB),@j=len(@chvA)
 while @j>0
  Begin
  If substring(@chvA,@j,1)=substring(@chvB,@j,1)
    select @same=@same+1
  set @j=@j-1
  End
 select @result=(Cast(Cast(@same*100/@i as decimal(10,2)) as varchar)+'%')
 return @result
End