T-SQL - 按字符比较字符串

问题描述

我需要使用 T-SQL 逐字符比较两个字符串.假设我有两个这样的字符串:

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

123456789    
212456789

每次字符不匹配时，我想增加变量@Diff +=1.在这种情况下，前三个字符不同.所以@Diff = 3(0 是默认值).

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).

感谢您的所有建议.

推荐答案

对于表中的列你不想使用逐行方法，试试这个:

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 小提琴演示