T-SQL – compare strings char by char

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.

Answer

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