SQL Server Performance

Compare strings

Discussion in 'Contribute Your SQL Server Scripts' started by benwilson, Jul 13, 2006.

  1. benwilson New Member

    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
  2. dineshasanka Moderator

    seems to be a usefull function.
    Just to inform this script will work for SQL Server 2005 not for 2000.

    ----------------------------------------

  3. ranjitjain New Member

    <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=':)' />]

Share This Page