Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как сопоставить текстовые поля по совпадению некоторых слов  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 


Выгрузили из файла в таблицу текстовые значения клиентов (где-то 250 строк).

Table1:

Клиент
Абрамян Москва
Элен ООО москва
Александрит ЗАО г. Тверь
Африкян Е.Э.ИП
......


Также есть справочник клиентов состоящий из несколько тысяч строк

Table2:

Клиент ID
ИП Абрамян Мигран (г.Москва) 12
ИП Абрамян А.А. (Гусиноозерск) 18
ООО "ЭЛЕН-ТУР" (г.Коркино) 25
ООО "Элен" (г.Москва) 30
ООО "Александрит" (г.Якутск)34
ООО "Александрит" (г.Санкт-Петербург)45
ООО "Александрит" (г.Оренбург)46
ООО "Александрит" (г. Москва)47
ЗАО "Александрит" (Тверь)50
ИП Африкян Е.Э. ( г.Москва )60
.....


Как сопоставить эти две таблицы чтобы присвоить значению поля Клиент из Table1, ID - значения из Table2.
28 июн 13, 17:30    [14498643]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
aleks2
Guest
Разобрать строки в таблицах на слова. Удалить разделители.
Сравнить по-словам.
Где больше совпало - там оно и есть.
29 июн 13, 12:06    [14500265]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Денис Б.
Member

Откуда: Екатеринбург
Сообщений: 158
покопайте около Ливенштайна (метод ищет различие между значениями)
1 июл 13, 07:18    [14502970]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Денис Б.
Member

Откуда: Екатеринбург
Сообщений: 158
или же есть такая метода:

CREATE FUNCTION [dbo].[StringCorrelation] --ищет различие между двумя строками и выдает коофициент похожести
(
 @string1 nvarchar(200)
,@string2 nvarchar(200)
)
RETURNS real


BEGIN
	
declare @str1 nvarchar(200)
declare @str2 nvarchar(200)

SET @str1 = (UPPER(replace(replace(@string1,'  ',' '),' ','')))
SET @str2 = (UPPER(replace(replace(@string2,'  ',' '),' ','')))	
	
declare @i int
declare @cor real
declare @str3 nvarchar(200)
if len(@str1) < len(@str2) begin
	set @str3 = @str2
	set @str2 = @str1
	set @str1 = @str3
end
set @cor = 0
set @i = 1
while @i <= len(@str2) begin
	if charindex(substring(@str2, @i, 1), @str1, 1) > 0 set @cor = @cor + 1 
	set @i = @i + 1
end
RETURN @cor * 2 / (len(@str1) + len(@str2))

END
1 июл 13, 07:21    [14502972]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
aleks2
Разобрать строки в таблицах на слова. Удалить разделители.
Сравнить по-словам.
Где больше совпало - там оно и есть.
так не получится, например Александрит ЗАО г.Тверь и ЗАО "Александрит" (г. Тверь)
Убираем все разделители получаем АлександритЗАОгТверь и ЗАОАлександритгТверь
и таких много случаев...они не будут совпадать.
1 июл 13, 09:56    [14503433]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
Денис Б.
или же есть такая метода:

CREATE FUNCTION [dbo].[StringCorrelation] --ищет различие между двумя строками и выдает коофициент похожести
(
 @string1 nvarchar(200)
,@string2 nvarchar(200)
)
RETURNS real


BEGIN
	
declare @str1 nvarchar(200)
declare @str2 nvarchar(200)

SET @str1 = (UPPER(replace(replace(@string1,'  ',' '),' ','')))
SET @str2 = (UPPER(replace(replace(@string2,'  ',' '),' ','')))	
	
declare @i int
declare @cor real
declare @str3 nvarchar(200)
if len(@str1) < len(@str2) begin
	set @str3 = @str2
	set @str2 = @str1
	set @str1 = @str3
end
set @cor = 0
set @i = 1
while @i <= len(@str2) begin
	if charindex(substring(@str2, @i, 1), @str1, 1) > 0 set @cor = @cor + 1 
	set @i = @i + 1
end
RETURN @cor * 2 / (len(@str1) + len(@str2))

END


так тоже не подходит..
пример
select General.dbo.StringCorrelation('Абрамян М. г.Москва','ИП Абрамян Мигран  (г.Москва)') 
select General.dbo.StringCorrelation('Гукасян Т.П. ИП г.Москва','ИП Абрамян Мигран  (г.Москва)')  
на выходе дает соответственно 0,8 и 0,82 соответственно..т.е. почему то где не совпадает дает больше коэф, где совпадает слово.
1 июл 13, 09:58    [14503441]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
aleks2
Guest
Чукча не читатель?
aleks2
1. Разобрать строки в таблицах на слова.
2. Удалить разделители.
1 июл 13, 09:58    [14503442]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
iap
Member

Откуда: Москва
Сообщений: 47084
minya13_85
aleks2
Разобрать строки в таблицах на слова. Удалить разделители.
Сравнить по-словам.
Где больше совпало - там оно и есть.
так не получится, например Александрит ЗАО г.Тверь и ЗАО "Александрит" (г. Тверь)
Убираем все разделители получаем АлександритЗАОгТверь и ЗАОАлександритгТверь
и таких много случаев...они не будут совпадать.
Кто Вам советовал только убрать все разделители?
Какой смысл Вы вкладываете в понятие "слово"?
Сколько слов получится в любом тексте, из которого убрали все разделители?
1 июл 13, 10:00    [14503455]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
iap
Кто Вам советовал только убрать все разделители?
Какой смысл Вы вкладываете в понятие "слово"?
Сколько слов получится в любом тексте, из которого убрали все разделители?
Я не совсем понял. Беру допустим тот же Александрит ЗАО г. Тверь, делю на 4 слова: Александрит, ЗАО, г, Тверь так? дальше беру таблицу клиентов, там этих Александритов куча..разбиваю их также на слова, получаю: ООО, Александрит,г,Якутск,ООО,Александрит,г,Санкт,Петербург,.....,ЗАО,Александрит,г,Тверь..И как мне это сопоставить?? Или я что-то недопонимаю?
1 июл 13, 10:16    [14503530]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Hooly-Gun
Member

Откуда:
Сообщений: 16
minya13_85,
вот еще тут
1 июл 13, 10:28    [14503604]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
iap
Member

Откуда: Москва
Сообщений: 47084
minya13_85
iap
Кто Вам советовал только убрать все разделители?
Какой смысл Вы вкладываете в понятие "слово"?
Сколько слов получится в любом тексте, из которого убрали все разделители?
Я не совсем понял. Беру допустим тот же Александрит ЗАО г. Тверь, делю на 4 слова: Александрит, ЗАО, г, Тверь так? дальше беру таблицу клиентов, там этих Александритов куча..разбиваю их также на слова, получаю: ООО, Александрит,г,Якутск,ООО,Александрит,г,Санкт,Петербург,.....,ЗАО,Александрит,г,Тверь..И как мне это сопоставить?? Или я что-то недопонимаю?
"Делю на 4 слова" - что в результате, по-Вашему?
По-моему, каждое из этих слов надо расположить на своей строке,
в строке должно быть поле ID исходной строки.
Тогда останется только сравнивать слова полученных строк с разными ID. Как? Ну, может, aleks2 уточнит?
При этом про учёт разных форм одного и того же слова, конечно, не может быть и речи.
1 июл 13, 10:34    [14503636]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
В ssis есть уже реализованная фишка для нечеткого поиска слов. Вроде для таких задач и создана.
1 июл 13, 10:58    [14503777]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Ramis
Member

Откуда:
Сообщений: 99
1)Получаешь строку из таблицы 1
2)Сохраняешь каждое слово отдельно из строки и запоминаешь сколько их
3)в цикле формируешь кусок запроса Case для каждого слова


на примере строки "Элен ООО москва" в результате должно получиться что то вроде этого:

Select ID From Table2 
where (case when ПолеКлиент like '%[^A-я]Элен[^A-я]%' or 
                ПолеКлиент like 'Элен[^A-я]%' or 
                ПолеКлиент like '%[^A-я]Элен' or  
                ПолеКлиент like 'Элен'  then 1 else 0 end 
       +
       case when ПолеКлиент like '%[^A-я]OOO[^A-я]%' or 
                ПолеКлиент like 'OOO[^A-я]%' or 
                ПолеКлиент like '%[^A-я]OOO' or  
                ПолеКлиент like 'OOO'  then 1 else 0 end 
      +
       case when ПолеКлиент like '%[^A-я]москва[^A-я]%' or 
                ПолеКлиент like 'москва[^A-я]%' or 
                ПолеКлиент like '%[^A-я]москва' or  
                ПолеКлиент like 'москва'  then 1 else 0 end) = 3 (Количество слов в строке)


Есть вероятность запрос вернет несколько строк
И так в цикле для всех строк из таблицы1
1 июл 13, 11:05    [14503824]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
aleks2
Guest
CREATE function [dbo].[f_StrToTableEx1](@str nvarchar(4000), @delimiter nvarchar(64)=',')
returns table as
return(
WITH str_nums ( n1, n2, Number ) 
AS 
( 
select  1-DATALENGTH(@delimiter)/2 as n1, charindex(@delimiter, @str+@delimiter) as n2, 0 as Number  
UNION ALL 
select n2 as n1, charindex(@delimiter, @str+@delimiter, n2+DATALENGTH(@delimiter)/2) as n2, Number+1 as Number
from str_nums
WHERE n2<len(@str)
) 
SELECT SUBSTRING(@str, n1+DATALENGTH(@delimiter)/2, n2-n1-1) as Value, Number, n1+1 as StartPosition FROM str_nums
)
go

CREATE function [dbo].[f_ReplaceDelimiters](@str nvarchar(4000))
returns nvarchar(4000)
as
begin
 select @str = replace(@str, ch, N' ') from dbo.Delimiters where charindex(ch, @str) > 0;
 while charindex(N'  ', @str)>0 select @str = replace(@str, N'  ', N' ');
 return @str
end
go

CREATE TABLE [dbo].[Delimiters](
	[ch] [nchar](1) NOT NULL,
	[new] [nvarchar](1) NOT NULL,
 CONSTRAINT [PK_Delimiters] PRIMARY KEY CLUSTERED 
(
	[ch] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go

insert dbo.Delimiters (ch, new)
select ')', ' '

insert dbo.Delimiters (ch, new)
select '(', ' '

insert dbo.Delimiters (ch, new)
select '.', ' '
go

declare @Table1 table(s nvarchar(1024), id int identity primary key clustered);

declare @Table2 table(s nvarchar(1024), id int identity primary key clustered);

insert @Table1(s)
select N'Абрамян М. г.Москва'
union all
select N'Гукасян Т.П. ИП г.Москва';

insert @Table2(s)
select'ИП Абрамян Мигран  (г.Москва)'
union all
select N'ИП Абрамян Мигран  (г.Москва)'
union all
select N'ИП Абрамян М  (г.Москва)';

select T1.id, dbo.f_ReplaceDelimiters(T1.s) 
from @Table1 T1 

select id, dbo.f_ReplaceDelimiters(s) 
from @Table2 T2

select * from dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(N'Абрамян М. г.Москва'), N' ')

select T1.id, w.Value  
from @Table1 T1 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T1.s), N' ') w

select T2.id, w.Value  
from @Table2 T2 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T2.s), N' ') w


select x1.id id1, x2.id id2, count(*) cnt, sum(len(x1.Value)) cnt2
from
(select T1.id, w.Value  
   from @Table1 T1 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T1.s), N' ') w
) x1
inner join 
(select T2.id, w.Value  
   from @Table2 T2 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T2.s), N' ') w
) x2
on x1.Value = x2.Value
group by x1.id, x2.id

declare @Table1 table(s nvarchar(1024), id int identity primary key clustered);

declare @Table2 table(s nvarchar(1024), id int identity primary key clustered);

insert @Table1(s)
select N'Абрамян М. г.Москва'
union all
select N'Гукасян Т.П. ИП г.Москва';

insert @Table2(s)
select'ИП Абрамян Мигран  (г.Москва)'
union all
select N'ИП Абрамян Мигран  (г.Москва)'
union all
select N'ИП Абрамян М  (г.Москва)';

-- это шоп понятнее было
--select T1.id, dbo.f_ReplaceDelimiters(T1.s) 
--from @Table1 T1 
--
--select id, dbo.f_ReplaceDelimiters(s) 
--from @Table2 T2
--
--select * from dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(N'Абрамян М. г.Москва'), N' ')
--
--select T1.id, w.Value  
--from @Table1 T1 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T1.s), N' ') w
--
--select T2.id, w.Value  
--from @Table2 T2 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T2.s), N' ') w

-- что-то вроде желаемого тредстартером
select x1.id id1, x2.id id2, count(*) cnt, sum(len(x1.Value)) cnt2
from
(select T1.id, w.Value  
   from @Table1 T1 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T1.s), N' ') w
) x1
inner join 
(select T2.id, w.Value  
   from @Table2 T2 cross apply dbo.f_StrToTableEx1(dbo.f_ReplaceDelimiters(T2.s), N' ') w
) x2
on x1.Value = x2.Value
group by x1.id, x2.id
1 июл 13, 11:38    [14504017]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
+ как-то так?
;with Table2 as (select * from (values
   ('ИП Абрамян Мигран (г.Москва)',12),
   ('ИП Абрамян А.А. (Гусиноозерск)',18),
   ('ООО "ЭЛЕН-ТУР" (г.Коркино)',25),
   ('ООО "Элен" (г.Москва)',30),
   ('ООО "Александрит" (г.Якутск)',34),
   ('ООО "Александрит" (г.Санкт-Петербург)',45),
   ('ООО "Александрит" (г.Оренбург)',46),
   ('ООО "Александрит" (г. Москва)',47),
   ('ЗАО "Александрит" (Тверь)',50),
   ('ИП Африкян Е.Э. ( г.Москва )',60)
	)temp_Table2(Клиент,ID)),
Table2_words as(
   select t.id, left(t.Клиент,l.last-1)word, right(t.Клиент, length-isnull(nullif(p.poz,0),length)) rest, poz
      from Table2 t
      -- cross apply(select replace(replace(replace(t.клиент,space(2),space(1)+char(1)),char(1)+space(1),''),char(1),''))n(name)
      cross apply(select LEN(t.Клиент))e(length)
      cross apply(select patindex('%[, ."''();]%',t.Клиент))p(poz)
      cross apply(select ISNULL(NULLIF(p.poz,0),length+1))l(last)
      where length>0 
   union all
   select w.id, left(w.rest,l.last-1)word, right(w.rest, length-isnull(nullif(p.poz,0),length)) rest, p.poz
      from Table2_words w
      cross apply(select LEN(w.rest))n(length)
      cross apply(select patindex('%[, ."''();]%',w.rest))p(poz)
      cross apply(select ISNULL(NULLIF(p.poz,0),length+1))l(last)
      where length>0 
   ),
Table1 as (select * from (values
   (1,'Абрамян Москва'),
   (2,'Элен ООО москва'),
   (3,'Александрит ЗАО г. Тверь'),
   (4,'Африкян Е.Э.ИП')
	)temp_Table1(id,Клиент)),
Table1_words as(
   select t.id, left(t.Клиент,l.last-1)word, right(t.Клиент, length-isnull(nullif(p.poz,0),length)) rest, poz
      from Table1 t
      -- cross apply(select replace(replace(replace(t.клиент,space(2),space(1)+char(1)),char(1)+space(1),''),char(1),''))n(name)
      cross apply(select LEN(t.Клиент))e(length)
      cross apply(select patindex('%[, ."''();]%',t.Клиент))p(poz)
      cross apply(select ISNULL(NULLIF(p.poz,0),length+1))l(last)
      where length>0 
   union all
   select w.id, left(w.rest,l.last-1)word, right(w.rest, length-isnull(nullif(p.poz,0),length)) rest, p.poz
      from Table1_words w
      cross apply(select LEN(w.rest))n(length)
      cross apply(select patindex('%[, ."''();]%',w.rest))p(poz)
      cross apply(select ISNULL(NULLIF(p.poz,0),length+1))l(last)
      where length>0 
   )
select top (1) with ties 
   (select Клиент from Table1 where id=w1.id) Поиск,
   (select Клиент from Table2 where id=w2.id) Найдено
from Table1_words w1
join Table2_words w2 on w2.word=w1.word
group by w1.id, w2.ID
order by ROW_NUMBER() over(partition by w1.id order by COUNT(*) desc)
ПоискНайдено
Элен ООО москваООО "Элен" (г.Москва)
Александрит ЗАО г. ТверьООО "Александрит" (г. Москва)
Абрамян МоскваИП Абрамян Мигран (г.Москва)
Африкян Е.Э.ИПИП Африкян Е.Э. ( г.Москва )

Естественно, справочник Table2_words готовится заранее и с индексом, возможно по CheckSum :)
1 июл 13, 11:44    [14504069]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
+ исправлено в последнем select:

select top (1) with ties 
   (select Клиент from Table1 where id=w1.id) Поиск,
   (select Клиент from Table2 where id=w2.id) Найдено
from Table1_words w1
join Table2_words w2 on w2.word=w1.word
where w1.poz!=1 and w2.poz!=1
group by w1.id, w2.ID
order by dense_rank() over(partition by w1.id order by COUNT(*) desc)
ПоискНайдено
Элен ООО москваООО "Элен" (г.Москва)
Александрит ЗАО г. ТверьЗАО "Александрит" (Тверь)
Абрамян МоскваИП Абрамян Мигран (г.Москва)
Африкян Е.Э.ИПИП Африкян Е.Э. ( г.Москва )
1 июл 13, 11:52    [14504140]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить текстовые поля по совпадению некоторых слов  [new]
Ramis
Member

Откуда:
Сообщений: 99
Cygapb-007,
почему сопоставил Александрит ЗАО г. Тверь и ООО "Александрит" (г. Москва) ???
1 июл 13, 11:52    [14504144]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить