T-SQL search two strings return first bad char-CodePudding

Is there a way in T-SQL to compare two strings and return the first char that is different or return 0 if they are alike?

CodePudding user response：

There is no built-in method of doing this. You could create a function using a WHILE loop, like this:

CREATE FUNCTION dbo.FindFirstDiffPosition(@a VARCHAR(MAX), @b VARCHAR(MAX))
RETURNS INT AS BEGIN
    DECLARE @i INT=1
    WHILE @i<=LEN(@a) AND @i<=LEN(@b) BEGIN
        IF SUBSTRING(@a,@i,1)<>SUBSTRING(@b,@i,1) BREAK
        SET @i=@i 1
    END
    IF @i>LEN(@a) AND @i>LEN(@b) SET @i=0
    RETURN @i
END

GO
CREATE TABLE SampleData (
    a VARCHAR(MAX),
    b VARCHAR(MAX)
)

INSERT INTO SampleData (a, b) VALUES
('This is a string', 'This is another string'),
('This is a string', 'This is a string, too'),
('This is a string', 'Something else'),
('This is a string', ''),
('Same string','Same string'),
('', '')

SELECT *, dbo.FindFirstDiffPosition(a,b)
FROM dbo.SampleData

CodePudding user response：

This looks like a good place to split a string into rows and then compare.

something like the following should hopefully give you enough to play with.

This uses an in-line numbers table purely for demo purposes, replace with a proper numbers table for a real-world application.

declare @string1 varchar(50)='abcdefghijkl', @string2 varchar(50)='abcdefgXijkl';

with n as (select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))n(n)),
c1 as (select Substring(@string1,n,1) c1, n n1 from n where n <=Len(@string1)),
c2 as (select Substring(@string2,n,1) c2, n n2 from n where n <=Len(@string2)),
 m as (
  select top(1)
    Concat(c1, ' at position ', n1, ' in string1 does not match ', c2, ' in string 2') Result
  from c1 full join c2 on n1 = n2 
  where IsNull(c1,'') != IsNull(c2,'') 
  order by n1
)
select * 
from m
union 
select 'All match' 
where @string1 = @string2;

Result: "h at position 8 in string1 does not match X in string 2"