sql server – Find the first mismatched character between two strings

There is a very similar question in another forum in which someone wants to compare matrices varchar 1 character at a time.

There are several responses that include iterating through character strings one character at a time, and long scripts that introduce new number tables into your database. And then there's a little jewel of response that uses bit comparison to detect the first difference between strings.

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

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

create FUNCTION dbo.fnFirstDifference (@FirstString VARCHAR (256), @ SecondString VARCHAR (256))
Int Returns
AS
START
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)
start

SELECT @var = CAST (CAST (@FirstChunk AS BINARY (8)) AS BIGINT) ^ CAST (CAST (@SecondChunk AS BINARY (8)) AS BIGINT),
@pos = PATINDEX (& # 39;%[^0]% & # 39 ;, 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;
finish

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

BACK @pos
FINISH

The original function returned a varchar that provides all matching characters, plus the first mismatched character, of string 1, then a hyphen, and then the same for string 2. For example: & # 39; Interesti-Intereste & # 39;. I have converted this to provide the position of the first mismatched character, so I returned 9 for the following example:

    select dbo.fnFIrstDifference (& # 39; Interesting & # 39 ;, & # 39; Interested & # 39;)

Adapting to the other cases of use is trivial.