create function CompareText  (@String1 varchar (50), @String2 varchar (50))
returns integer
--Function CompareText
--blindman 4/2005, Adapted from MS Access algorithm developed 1997
--Returns value between 0 and 100 indicating the similarity between two character strings.
--usage: select * from [Table] where dbo.CompareText([ColumnValue], 'SearchString') > 80
----blindman, 6/5/2008: Added space before strings to improve accuracy.

begin
--Test parameters
-- declare	@String1 varchar(50)
-- declare	@String2 varchar(50)
-- set	@String1 = 'Bruce Alan Lindman'
-- set	@String2 = 'Lindman, Bruce Alan Sr.'

declare @Possibles integer
declare @Hits integer
declare @Counter integer

set	@String1 = ' ' + ltrim(rtrim(@String1))
set	@String2 = ' ' + ltrim(rtrim(@String2))

set @Possibles = len(@String1) + len(@String2) - 2
set @Hits = 0

set @Counter = len(@String1)-1
while @Counter > 0
	begin
	  if charindex(substring(@String1, @Counter, 2), @String2) > 0 set @Hits = @Hits + 1
	  set @Counter = @Counter - 1
	end

set @Counter = len(@String2)-1
while @Counter > 0
	begin
	  if charindex(substring(@String2, @Counter, 2), @String1) > 0 set @Hits = @Hits + 1
	  set @Counter = @Counter - 1
	end

return (100*@Hits)/@Possibles
--select (100*@Hits)/@Possibles

end