当前位置: 动力学知识库 > 问答 > 编程问答 >

tsql - SQL find characters different in two fields

问题描述:

I have a table with two fields: firstnamedb1 and firstnamedb2

I need to get those that doesnt match but also want to know how many characters are different.

For example, if I have:

firstnamedb1 firstnamedb2

RICHARD RICHRAD

Then I know that is a NO MATCH result but also I know that only 2 characters are different.

That way I would know that "maybe" that NO MATCH result was a misstype error and can be fixed manually very easy.

Hope someone can help me. Thanks a lot

网友答案:
select firstnamedb1, firstnamedb2, dbo.numdiffchars(firstnamedb1, firstnamedb2)
from yourTable
where firstnamedb1 <> firstnamedb2


create function dbo.numdiffchars(@n1 varchar(60), @n2 varchar(60))
returns int
as begin
  declare @baselen int,
          @extension int,
          @xpos int,
          @numdiff int;
  if len(@n1) <= len(@n2) select @baselen = len(@n1)
  else select @baselen = len(@n2)

  if len(@n1) < @baselen select @extension = (@baselen - len(@n1))
  else if len(@n2) < @baselen select @extension = (@baselen - len(@n2))

  select @xpos = 0, @numdiff = 0;
  while @xpos < @baselen begin
    select @xpos = @xpos + 1
    if substring(@n1, @xpos, 1) <> substring(@n2, @xpos, 1)
      select @numdiff = @numdiff + 1
  end

  return @numdiff + @extension
end

If this solves your problem please mark as answer.

分享给朋友:
您可能感兴趣的文章:
随机阅读: