Geek asks and answers

Find the first character position that differs between the two lines

This is the question the decision was made using bitwise XORin PHPto first find the different Charindex two strings. How to implement this solution in a SQL query or is there a better solution for this problem?

+3
source share
2 answers

Update : here Sql XOR :

only for a line with the letter len 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 small nice result:

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
+1
source

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

, ( PHP), SQL - , CHAR, :

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'

:

Difference at position 5 A:o B:a

, UDF , , , , NULL.

0

More articles:


All Articles