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.