Find first character position that is different between two strings

Question

In this Question the accepted solution using bitwise XOR in PHP to find first different Charindex of two string. How can implement this solution in SQL query or is there a better solution for this problem?

While it *can* be done in T-SQL (at least 4 different ways that I can think of), there is no efficient way using only SQL. If you need for it to be done in the SQL Server, then your best bet would be to write a SQLCLR function. — RBarryYoung, Feb 26 '14 at 14:08

jozi · Answer 1 · 2014-02-26T13:58:14.377

Update: Here's Sql XOR solution:

only for string with len letter than 8

DECLARE @A VARCHAR(50)
DECLARE @B VARCHAR(50)
DECLARE @Pos INT

set @A='ABrC12@4'
set @B='ABrC1234'

SET @Pos=( select 
   case 
   when len(@A)<>LEN(@B) then -1
   when @A=@B then 0
   ELSE 1+PATINDEX('%[1-9]%',
 CONVERT(varchar(max),cast(cast(CAST(@A AS varbinary) as BIGINT) ^
                           cast(CAST(@B AS varbinary)  as BIGINT) 
                      as varbinary ),2))/2
   end)
Print @Pos

You can use a little nice result with this:

create  FUNCTION [dbo].[fnFirstPosDif]
(
    @Word as Nvarchar(70),
    @Word2 as Nvarchar(70)
)
RETURNS INT
AS
BEGIN
declare  @Strings2 TABLE
(
FirstPosDif INT
)
declare @FirstPosDif as int

 ;with C as
(
  select @Word as Word,@Word2 as Word2 ,0 as Iteration
  union all
  select cast(substring(@Word,Iteration+1,1)as Nvarchar(70)) as Word,cast(substring(@Word2,Iteration+1,1)as Nvarchar(70)) as Word2,Iteration + 1  from C 
  WHERE  Iteration < len(@Word) and cast(substring(@Word,Iteration+1,1)as Nvarchar(70))=cast(substring(@Word2,Iteration+1,1)as Nvarchar(70))
)
insert into @Strings2(FirstPosDif) select  MAX(Iteration) as FirstPosDif from C
 set @FirstPosDif=(select top 1 FirstPosDif from @Strings2)

return @FirstPosDif
END

Very useful! I just needed a function just like this for a some client work and this worked out perfectly! — Ryan Harris, Oct 29 '20 at 08:47

score 0 · Answer 2 · answered Feb 26 '14 at 09:17

I do not believe that there is an equivalent to any of the methods shown in the question you referenced for SQL Server.

The only way appears to be to manually walk through the string (as per the PHP suggestion), but in SQL it is even more laborious due to the fact that you cannot address CHAR types as if they were arrays:

DECLARE @A NVARCHAR(50)
DECLARE @B NVARCHAR(50)
DECLARE @I INT
DECLARE @Pos INT

SET @A = 'Hello World!'
SET @B = 'Hella World!'
SET @I = 0
SET @Pos = 0

-- Loop through each character
WHILE (@I < LEN(@A) AND @I < LEN(@B) AND @Pos = 0)
BEGIN
    -- See if the characters at this position differ
    IF (SUBSTRING(@A, @I, 1) != SUBSTRING(@B, @I, 1))
        SET @Pos = @I

    SET @I = @I + 1
END

IF (@Pos > 0)
    PRINT 'Difference at position ' + CAST(@Pos AS VARCHAR) + ' A:' + SUBSTRING(@A, @Pos, 1) + ' B:' + SUBSTRING(@B, @Pos, 1)
ELSE
    PRINT 'Strings are the same'

Running this as per the above would yield the following output:

Difference at position 5 A:o B:a

Note that you could put this into a UDF for simplicity if you use it often, and additionally I have not included checks for NULL values.

Find first character position that is different between two strings

2 Answers2