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