Xrhrft

Posting this with solution provided.

The use case could be framed in a number of ways:

• Return all characters that match at the start of two strings, until they do not match


• How many characters match between two strings before one character doesn't match?


• What is the first character that doesn't match between two strings?


• etc.

For example:

If I have the strings 'Interesting' and 'Interested' they are similar for all the characters 'Interest' and then the first string ends in 'ing' while the second ends in 'ed'. Therefore, they have 8 characters in common, the first non-matching character is the 9th and the identical string is 'Interest'.

For the question in the title, the first non-matching character is number 9.

There is a very similar question on another forum in which somebody wants to compare varchar arrays 1 character at a time.

There are various answers there that include iterating through strings one character at a time, and lengthy scripts that introduce new tables of numbers into your database. And then there is a little gem of an answer that uses bit-wise comparison to detect the first difference between strings.

To do so, that solution converts each character of each string into binary representation. Due to the limit on the number of numerals permitted within the bigint data type there is the short note that this "is a nice function that works for up to 8 characters!"

Of course, extending this to support longer strings involves the trivial step of breaking the supplied strings into 8 character chunks - which is the solution I provide here.

create FUNCTION dbo.fnFirstDifference(@FirstString VARCHAR(256),@SecondString VARCHAR(256))

RETURNS int

AS

BEGIN

DECLARE @var VARBINARY(8),

@firstChunk varchar(8),

@secondChunk varchar(8),

@chunkStart int,

@loopCount int,

@pos TINYINT



set @chunkStart = 1

set @loopCount = -1;

set @pos = 0;



set @firstChunk = substring(@firstString, @chunkStart, 8);

set @secondChunk = substring(@secondString, @chunkStart, 8);



while @pos = 0 and (datalength(@firstChunk) > 0 or datalength(@secondChunk) > 0)

    begin



        SELECT @var = CAST(CAST(@FirstChunk AS BINARY(8)) AS BIGINT) ^ CAST(CAST(@SecondChunk AS BINARY(8)) AS BIGINT),

        @pos = PATINDEX('%[^0]%', SUBSTRING(master.dbo.fn_sqlvarbasetostr(@var), 3, 32)),

        @pos = (1 + @pos) / 2



        set @chunkStart = @chunkStart + 8;

        set @firstChunk = substring(@firstString, @chunkStart, 8);

        set @secondChunk = substring(@secondString, @chunkStart, 8);

        set @loopCount = @loopCount + 1;

    end



if @pos <> 0 set @pos = (@loopCount * 8) + @pos;



RETURN @pos

END

The original function returned a varchar providing all matching characters, plus the first non-matching character, from string 1, then a hyphen, and then likewise for string 2. For example: 'Interesti-Intereste'. I have converted this to provide the position of the first non-matching character - thus returning 9 for the following example:

 select dbo.fnFIrstDifference('Interesting', 'Interested')

Adapting to the other use cases is trivial.