Compare strings

Takes 2 strings as input and returns an int saying how many characters are different between the 2 strings..i use it to pick up typos and still join, etc if there is a typo

CREATE FUNCTION [dbo].[CompareStrings_Fn] (@FirstString varchar(max), @SecondString varchar(max))
RETURNS INT
AS

BEGIN

/*
Returns a count of the number of characters different in the 2 strings passed.

If each string contains the same number of each letters and there are 2 differences,
this will be counted as 1 difference as it is likely a typo: EG 'String' and 'Strnig'
*/

DECLARE @Difference AS INT,
@MaxLength AS INT,
@Position AS INT

--Create Temporary table to hold the count of letters in words
DECLARE @LettersInString TABLE
(
Letter CHAR(1) NOT NULL,
FirstStringCount INT NOT NULL DEFAULT 0,
SecondStringCount INT NOT NULL DEFAULT 0
)

--Difference is the counter of characters that are different in the 2 strings
SET @Difference = 0
--Position is the position of the character in the string that is being compared
SET @Position = 1

--determine the length of the longest string
IF len(@FirstString) < len(@SecondString)
SET @MaxLength = len(@SecondString)
ELSE
SET @MaxLength = len(@FirstString)

--loop through each string until the maximum length is reached
WHILE @Position <= @MaxLength
BEGIN
--check to see if the character in the first string is in the temp table. If not add
IF NOT EXISTS (SELECT Letter FROM @LettersInString WHERE Letter = SUBSTRING(@FirstString, @Position, 1))
INSERT INTO @LettersInString SELECT SUBSTRING(@FirstString, @Position, 1), 0, 0

--add 1 to the count of the character in the temp table
UPDATE @LettersInString
SET FirstStringCount = FirstStringCount + 1
WHERE Letter = SUBSTRING(@FirstString, @Position, 1)

--check to see if the character in the second string is in the temp table. If not add
IF NOT EXISTS (SELECT Letter FROM @LettersInString WHERE Letter = SUBSTRING(@SecondString, @Position, 1))
INSERT INTO @LettersInString SELECT SUBSTRING(@SecondString, @Position, 1), 0, 0

--add 1 to the count of the character in the temp table
UPDATE @LettersInString
SET SecondStringCount = SecondStringCount + 1
WHERE Letter = SUBSTRING(@SecondString, @Position, 1)

--check to see if the same position in each string holds the same character
IF SUBSTRING(@FirstString, @Position, 1) <> SUBSTRING(@SecondString, @Position, 1)
SET @Difference = @Difference + 1

--move to the next position
SET @Position = @Position + 1
END

--If each string contains the same number of each letters and there are 2 differences, this will be counted as 1 difference as it is likely a typo: EG 'String' and 'Strnig'
IF (SELECT COUNT(Letter) FROM @LettersInString WHERE FirstStringCount <> SecondStringCount) = 0 AND @Difference = 2
SET @Difference = 1

RETURN @Difference
END


'I reject your reality and substitute my own' - Adam Savage

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br /><br />Just to inform this script will work for SQL Server 2005 not for 2000.<br />----------------------------------------<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Yes but i guess just replacing <br />CREATE FUNCTION [dbo].[CompareStrings_Fn] (@FirstString varchar(max), @SecondString varchar(max))<br />with <br />CREATE FUNCTION [dbo].[CompareStrings_Fn] (@FirstString varchar(8000), @SecondString varchar(8000))<br /><br />will work in sql 2000 as well[<img src='/community/emoticons/emotion-1.gif' alt='' />]