T-SQL - compare strings char by char

Question

I need to compare two strings character by character using T-SQL. Let's assume i have twor strings like these:

123456789    
212456789

Every time the character DO NOT match, I would like to increase the variable @Diff +=1. In this case the first three characters differ. So the @Diff = 3 (0 would be default value).

Thank you for all suggestions.

what happens when the two variables are of different length? — Luis LL, Sep 02 '13 at 14:38
Did you see [How to compare if two strings contain the same words in T-SQL for SQL Server 2008?](http://stackoverflow.com/questions/9740256/how-to-compare-if-two-strings-contain-the-same-words-in-t-sql-for-sql-server-200) — huMpty duMpty, Sep 02 '13 at 14:39
do you have these two strings as variables or as columns in some table? are they always length 9? — Roman Pekar, Sep 02 '13 at 14:41
Columns in table. The length of strings will not differ and their length will always be 9. — Rutz, Sep 02 '13 at 14:44

score 4 · Answer 1 · answered Sep 02 '13 at 14:45

This code should count the differences in input strings and save this number to counter variable and display the result:

declare @var1 nvarchar(MAX)
declare @var2 nvarchar(MAX)
declare @i int
declare @counter int

set @var1 = '123456789'
set @var2 = '212456789'
set @i = LEN(@var1)
set @counter = 0

while @i > 0
begin
   if SUBSTRING(@var1, @i, 1) <> SUBSTRING(@var2, @i, 1)
   begin 
   set @counter = @counter + 1
   end
   set @i = @i - 1
end

select @counter as Value

If I helped you, please select post as answer to help other people with similar problems. Thank you in advance. — ksalk, Sep 02 '13 at 14:57

score 4 · Accepted Answer · answered Sep 02 '13 at 14:50

for columns in table you don't want to use row by row approach, try this one:

with cte(n) as (
    select 1
    union all
    select n + 1 from cte where n < 9
)
select
    t.s1, t.s2,
    sum(
      case
      when substring(t.s1, c.n, 1) <> substring(t.s2, c.n, 1) then 1
      else 0
      end
    ) as diff
from test as t
    cross join cte as c
group by t.s1, t.s2

=>sql fiddle demo

score 1 · Answer 3 · edited Jun 03 '20 at 03:37

The below query compares, shows the different characters and bring you the count of differences

Declare @char1 nvarchar(1), @char2 nvarchar(1), @i int = 1, @max int
Declare @string1 nvarchar(max) = '123456789'
        , @string2 nvarchar(max) = '212456789'
Declare @diff_table table (pos int , string1 nvarchar(50) , string2 nvarchar(50), Status nvarchar(50))
Set @max = (select case when len(@String1+'x')-1 > len(@string2+'x')-1 then len(@String1+'x')-1 else len(@string2+'x')-1 end)
while @i < @max +1

        BEGIN
        Select @char1 = SUBSTRING(@string1,@i,1), @char2 = SUBSTRING(@string2,@i,1)
        INSERT INTO @diff_table values 
        (
            @i,
            case when UNICODE(@char1) is null then '' else concat(@char1,' - (',UNICODE(@char1),')') end,
            case when UNICODE(@char2) is null then '' else concat(@char2,' - (',UNICODE(@char2),')') end,
            case when ISNULL(UNICODE(@char1),0) <> isnull(UNICODE(@char2),0) then 'CHECK' else 'OK' END
        )
        set @i+=1
        END

Select * from @diff_table
Declare @diff int = (Select count(*) from @diff_table where Status = 'Check')
Select @diff 'Difference'

The output will be like this:

Output

T-SQL - compare strings char by char

3 Answers3

Linked