select dbo.comparetext('John Smith', 'Smith, John')
----------- 
73

select dbo.comparetext(' John Smith ', ' Smith, John ')
----------- 
90

select dbo.comparetext(' vu a la ', ' Voila ')
----------- 
41

quote:

---

Originally posted by marat

As I promised, below is a string comparison function I developed and was using myself for years.
I put matching on the first and last characters as a parameters to let to do comparison test if anybody is interested.
I was using comparison on the first and last characters as a compalsory option(hardcoded).
I believe this function is generic enough :-)
---

if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[fn_CompareThePair]'))
drop function [dbo].[fn_CompareThePair]
GO
--
create function fn_CompareThePair
(@String1 varchar (100), @String2 varchar (100), @FirstIn bit = 1, @LastIn bit = 1)
returns integer
/*************************************************************************************************
Function CompareThePair
This function accepts two string values and returns an integer value between
zero and one hundred indicating the similarity between the two string. This
algorithm was developed specifically to search for the spelling
variations of the names and addresses. For this purpose,
it is superior to SOUNDEX, which searches only for similar sounding words.
SOUNDEX fails when dealing with not Latin names(asian names as an example)
or with their interpretations.
Usage: select dbo.CompareThePair('Smith', 'Shmidth', 1, 1)
Input Parameters
@String1 first string
@String2 second string
@FirstIn option to include comparison on the first character
@LastIn option to include comparison on the last character
--History
* Developed by marat 21/04/2008 based on the string matching algorithm used for address and name matching.

****************************************************************************************************/
begin

declare @Target integer
declare @Hits integer
declare @ExHits integer
declare @Pos integer
declare @Index integer
declare @Cursor integer
declare @Keep integer

--
set @Hits = 0
set @ExHits = 0
--
if @FirstIn > 0
begin
if left(@string1,1) = left(@string2,1) set @ExHits = @ExHits + 1
end
if @LastIn > 0
begin
if right(@string1,1) = right(@string2,1) set @ExHits = @ExHits + 1
end


set @Target =
case when len(@String1) >= len(@String2) then len(@String1) -1 + @FirstIn + @LastIn
else len(@String2) -1 + @FirstIn + @LastIn
end
--Run I
set @Pos = 1
set @Cursor = 1
while @Pos < len(@String1)
begin
set @Index = charindex(substring(@String1, @Pos, 2), @String2, @Cursor)
if @Index > 0
begin
set @Cursor = @Index + 1
set @Hits = @Hits + 1
end
set @Pos = @Pos + 1
end

set @Keep = @Hits --keep first result
--Run II
set @Pos = 1
set @Cursor = 1
set @Hits = 0
while @Pos < len(@String2)
begin
set @Index = charindex(substring(@String2, @Pos, 2), @String1, @Cursor)
if @Index > 0
begin
set @Cursor = @Index + 1
set @Hits = @Hits + 1
end
set @Pos = @Pos + 1
end

finish:
if @Keep > @Hits set @Hits = @Keep
return 100*(@Hits + @ExHits)/@Target
end

---