/*************************************** **********************
** File: fncompare. SQL
** Name: fncompare
** Description: compare two strings, return the alike degree with the percentage.
Compares two strings and returns the percentage of similarity.
** Return values: @ result
** Parameters: @ chva, @ chvb
** Author: mygodness)
** Date: 2005-1-12
** Use method: Select DBO. fncompare ('abcdefg', 'defdefdefg ')
**************************************** *********************/
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 (@ same * 100/@ I as decimal (10, 2) as varchar) + '% ')
Return @ result
End