Hi Adam,
I suggest you to try string matching function I developed and was using for years. This function compares two strings based on sequence of letters in the strings.
if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[fn_CompareThePair]'))
drop function [dbo].[fn_CompareThePair]
GO
--
create function dbo.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.fn_CompareThePair('margin', 'arming', 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 developed in 2003 and 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
GO