set nocount on
drop table #Ch
create table #Ch(C char( 1 ) primary key, K int)
insert into #Ch
select '1' C, 0  K union all
select '2', 0  union all select '3', 0  union all select '4', 0  union all select '5', 0  union all
select '6', 0  union all select '7', 0  union all select '8', 0  union all select '9', 0  union all
select '0', 0  union all
select 'q', 0  union all select 'w', 0  union all select 'e', 0  union all select 'r', 0  union all
select 't', 0  union all select 'y', 0  union all select 'u', 0  union all select 'i', 0  union all
select 'o', 0  union all select 'p', 0  union all select 'l', 0  union all select 'k', 0  union all
select 'j', 0  union all select 'h', 0  union all select 'g', 0  union all select 'f', 0  union all
select 'd', 0  union all select 's', 0  union all select 'a', 0  union all select 'z', 0  union all
select 'x', 0  union all select 'c', 0  union all select 'v', 0  union all select 'b', 0  union all
select 'n', 0  union all select 'm', 0  union all 
select 'ё', 0  union all select 'й', 0  union all select 'ц', 0  union all select 'у', 0  union all
select 'к', 0  union all select 'е', 0  union all select 'н', 0  union all select 'г', 0  union all
select 'ш', 0  union all select 'щ', 0  union all select 'з', 0  union all select 'х', 0  union all
select 'ъ', 0  union all select 'э', 0  union all select 'ж', 0  union all select 'д', 0  union all
select 'л', 0  union all select 'о', 0  union all select 'р', 0  union all select 'п', 0  union all
select 'а', 0  union all select 'в', 0  union all select 'ы', 0  union all select 'ф', 0  union all
select 'я', 0  union all select 'ч', 0  union all select 'с', 0  union all select 'м', 0  union all
select 'и', 0  union all select 'т', 0  union all select 'ь', 0  union all select 'б', 0  union all
select 'ю', 0 
 -- В данной табле гранится временные данные
 
drop table #G
select rtrim(Name) name, rtrim(Name) name2, cast('' as varchar( 100 )) Sname, idGood into #G from good

declare Cur cursor LOCAL for select name from #G
OPEN Cur
 declare @i int, @CC char( 1 ), @N varchar( 255 ),
         @C char( 1 ), @K int,
 @N2 varchar( 255 ),  -- значение функции № 1
 
 @sN varchar( 100 )   -- значение функции № 2
 
 fetch next from Cur into @N
 while @@fetch_status= 0  begin
          update #Ch set K= 0 
            select @i=len(@N), @N2=@N
            while @i<> 0  begin
             set @CC=substring(@N,@i, 1 )
             update #ch set K=K+ 1  where C=@CC
             
 --   print str(@E)+str(@R)+str(@A)+char(@G)
 
             select @i=@i- 1 
             if @cc not in ('0','1','2','3','4','5','6','7','8','9') set @N2=replace(@N2,@cc,'_')
            end
          declare Cur2 cursor LOCAL for select C,K from #Ch where K<> 0  order by  1 
          OPEN Cur2
           select @sN=''
           fetch next from Cur2 into @C, @K
           while @@fetch_status= 0  begin
            set @sN=@sN+@c+char(@K+ 65 )
            fetch next from Cur2 into @C, @K
           end 
          close Cur2
          deallocate Cur2

  UPDATE #G SET sName=@sN, name2=@N2 WHERE CURRENT OF Cur
 -- проапдатели оба ключа
 
  fetch next from Cur into @N
 end 
close Cur
deallocate Cur

 -- Временная табла для ускорения процесса
 
select g1.*
into #H
from #G g1
 where exists(select * from #G g2 where g1.sname=g2.sname and g1.idGood<>g2.idgood)

 -- Подозрительные на двойников
 
 -- 1 отлов проблем с двойными пробелами, лишние тире, скобки и т.п. (тогда просто разная длина наименования)
 
 -- 2  тире вместо пробела, или точка вместо тире и т.п. 
 

select g1.name N1,g2.name N2, g1.idgood id1, g2.idgood id2
into #H2
 from #H g1, #H g2
where (g1.sname=g2.sname and g1.idGood>g2.idgood and len(g1.name)<>len(g2.name))
   or (g1.sname=g2.sname and g1.idGood>g2.idgood and len(g1.name)=len(g2.name) and g1.name2=g2.name2)
order by  1 
drop table #H

 -- Навешивание дополнительного условия
 
 -- в случае клиента это будет INN, р/с и т.д.
 
 -- в случае товара это производитель
 
select rtrim(b1.name)+' - '+rtrim(g1.name),
       rtrim(b2.name)+' - '+rtrim(g2.name),
       h.id1, h.id2
 from #h2 H
  inner join Good G1 on g1.idgood=id1
  inner join brand B1 on b1.idBrand=g1.idBrand

  inner join Good G2 on g2.idgood=id2
  inner join brand B2 on b2.idBrand=g2.idBrand

where b1.idBrand=b2.idBrand
 --drop table #H2
 
set nocount off
go