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