SQL скрипт для извлечения страны из свободного текстового поля
Я пытаюсь создать SQL UDF или инструкцию, чтобы проанализировать поле произвольного текста и найти из него название страны, но я не могу сделать это успешно.
Чтобы дать вам полный контекст, у меня есть таблица транзакций (tbltransactions ниже), которая содержит детали транзакции, и одно из полей - это поле с произвольным текстом. В идеале это должно содержать имя получателя, адрес получателя и страну получателя (в таком порядке). Но, как и следовало ожидать, со свободным текстовым полем, есть всевозможные комбинации. Это также означает, что название страны может быть неправильно написано, сокращено, сокращено или полностью отсутствует. К счастью, большинство транзакций имеют страны, указанные в конце текстового блока! В таблице есть еще одно поле, где пользователь вводит трехзначный код страны (обязательно). Это может совпадать или не совпадать с тем, что он ввел в поле свободного текста. Ниже приведены фиктивные данные в таблице:
TransID ISOCode BeneAddress
------------------- -----------
20 IRN aaaa bb cccc Islamic Rupublic of Iran
19 IRN aaaa bb cccc Iran, Islamic Republic of
Теперь я составил справочную таблицу (tblCountryMappings), в которой хранится список всех стран и возможных вариаций их названий (ну, большинство из них!).
Например. "Македония, Республика", "Македония, Бывшая Югославская Республика", "Македония", "Македония" и т. Д.
Ниже приведены фиктивные данные для этой таблицы:
ID ISONumericCode countryName matchIdentifier matchIdentifierType
----------------------------------------------------------------------------------------------
209 364 Iran, Islamic Republic of IR ISOAlphaCode_2
210 364 Iran, Islamic Republic of IRN ISOAlphaCode_3
495 364 Iran, Islamic Republic of Iran Short_Name
1163 364 Iran, Islamic Republic of Iran, Islamic Republic of Original_Name
1309 364 Iran, Islamic Republic of Islamic Rupublic of Iran Alternate_Name
Как вы можете видеть, между таблицами существует соотношение один ко многим. Цель состоит в том, чтобы иметь возможность проанализировать транзакцию и определить, для какой страны она была предназначена (восновном на основе свободного текста, а не только кода ISO). Например, мы должны видеть, что транзакция 123 имеет "Ирак" в коде ISO и "Иран" в свободном тексте, а сопоставление в свободном тексте было в ISO 3-символьном "IRN". Мне также нужно убедиться, что сопоставление работает в граничных ситуациях (например, конец строки, заключенный в кавычки), но не в том случае, если оно находится в середине блока текста (например, не соответствует двухсимвольному коду Саудовской Аравии "SA" для кто-нибудь называется "Самуил").
Я написал этот базовый сценарий для извлечения последнего слова из произвольного текста, и его можно затем использовать для присоединения к matchIdentifier в tblCountryMappings, но, очевидно, это крайне неудачная попытка.
select
beneaddress
,SUBSTRING(beneaddress,
case when CHARINDEX(' ',REVERSE(beneaddress)) = 0 then 1
else LEN(beneaddress) - CHARINDEX(' ',REVERSE(LTRIM(RTRIM(beneaddress))))+2
end
,LEN(beneaddress)) as Country
from
tblTransactions
Если бы вы могли помочь мне построить это решение, оно будет очень признательно. Прошу прощения, если я нарушил какие-либо правила публикации, так как это мой первый раз. Не стесняйтесь спрашивать дополнительную информацию, и я опубликую ее в ближайшее время.
Заранее большое спасибо.
ура
2 ответа
Я сомневаюсь, что существует идеальное решение, поскольку я могу представить странные сценарии, в которых названия отдельных улиц или штатов могут быть похожи на названия некоторых стран. Сказав, что вы можете присоединить свою таблицу поиска к использованию таблицы транзакций LIKE заявление. Таким образом, вы можете использовать регулярные выражения, чтобы соответствовать стране в адресе. Название страны будет либо в конце, либо разделено спереди или в конце символом ',' или пробелом. Это должно несколько упростить ваш запрос, но, как я уже говорил, он не будет идеальным.
В следующем примере показано, как будет выглядеть запрос.
DECLARE @tbltransactions TABLE
(
TransID INT
,ISOCode NVARCHAR(3)
,BeneAddress NVARCHAR(100)
)
DECLARE @tblCountryMappings TABLE
(
ID INT IDENTITY
,CountryName NVARCHAR(100)
,MatchIdentifier NVARCHAR(100)
)
INSERT INTO @tbltransactions
(
TransID
,ISOCode
,BeneAddress
)
VALUES
(1 ,'IRN' ,'aaaa bb cccc Islamic Rupublic of Iran') ,
(2 ,'IRN' ,'aaaa bb cccc "Iran", Islamic Republic of'),
(3 ,'IRN' ,'aaRSAbb cccc IRN'),
(4 ,'IRN' ,'aaaa bb cccc IR'),
(5 ,'IRN' ,'aaaa bb cccc The Country of Fred')
INSERT INTO @tblCountryMappings
(
CountryName
,MatchIdentifier
)
VALUES
('Iran, Islamic Republic of', 'IR'),
('Iran, Islamic Republic of', 'IRN'),
('Iran, Islamic Republic of', 'Iran'),
('South Africa, Republic of', 'RSA'),
('South Africa, Republic of', 'R.S.A.'),
('South Africa, Republic of', 'South Africa')
SELECT T.TransID
,T.BeneAddress
,ISNULL(M.CountryName, '< Could not match country>') AS CountryName
,M.MatchIdentifier
FROM @tbltransactions T
LEFT OUTER JOIN @tblCountryMappings M ON
(
(T.BeneAddress LIKE '%[, "]' + M.MatchIdentifier + '[, "]%') -- Match any address which contains a word that start with a comma or space or quote ([, "]) and is then followed by the MatchIdentifier and then end with either a comma or space or quote.
OR
(T.BeneAddress LIKE '%[, "]' + M.MatchIdentifier ) -- Match any address which contains a word that start with a comma or space or quote ([, "]) and is then ends with the MatchIdentifier.
OR
(T.BeneAddress LIKE M.MatchIdentifier + '[, "]%') -- Match any address which contains a word that start with the MatchIdentifier and then end with either a comma or space or quote.
OR
(T.BeneAddress LIKE M.MatchIdentifier ) -- Match the address with an exact match of the MatchIdentifier
)
В приведенном выше примере sql сопоставит BeneAddress с регулярным выражением, созданным на основе значения поля MatchIdentifier.
Например, в tblCountryMappings поле MatchIdentifier будет иметь следующие значения для Ирана.
- инфракрасный
- IRN
- Иран
Это сгенерирует следующие регулярные выражения:
- % [, "] IR [,"]% - сопоставляет любую строку, которая содержит слово, начинающееся с запятой или пробела или кавычки ([, "]), за которым следует IR, а затем заканчивается запятой или пробелом или цитаты.
- % [, "] IRN [,"]% - сопоставляет любую строку, содержащую слово, которое начинается с запятой или пробела или кавычки ([, "]), затем следует IRN и затем заканчивается запятой или пробелом или цитаты.
- % [, "] Iran [,"]% - соответствует любой строке, которая содержит слово, начинающееся с запятой или пробела или кавычки ([, "]), за которым следует Иран, а затем конец запятой или пробела или цитаты.
Чтобы соответствовать возможности того, что страна может находиться в конце строки, мы добавляем дополнительный OR условие, в котором для конца не определено соответствие шаблону. Подобно тому, чтобы соответствовать возможности того, что название страны может быть в начале строки, мы добавляем дополнительный OR условие, при котором для начала не определено совпадение с образцом.
Если я понимаю ваш вопрос.
Следующее вернет самое высокое число совпадений. Для этого требуется функция PARSING, перечисленная ниже:
Создать образец данных
Declare @YouTable table (TransID int,ISOCode varchar(50),BeneAddress varchar(500))
Insert Into @YouTable values
(20,'IRN','aaaa bb cccc Islamic Rupublic of Iran'),
(19,'IRN','aaaa bb cccc Iran, Islamic Republic of')
Declare @ISO table (ID int,ISONumericCode int,countryName varchar(50),matchIdentifier varchar(50),matchIdentifierType varchar(50))
Insert Into @ISO values
(209 ,364,'Iran, Islamic Republic of','IR', 'ISOAlphaCode_2'),
(210 ,364,'Iran, Islamic Republic of','IRN', 'ISOAlphaCode_3'),
(495 ,364,'Iran, Islamic Republic of','Iran', 'Short_Name'),
(1163 ,364,'Iran, Islamic Republic of','Iran, Islamic Republic of','Original_Name'),
(1309 ,364,'Iran, Islamic Republic of','Islamic Rupublic of Iran' ,'Alternate_Name')
Актуальный SQL
;with cteBase as (
Select A.*,B.*,C.*
From @YouTable A
Cross Apply (Select * from [dbo].[udf-Str-Parse](A.BeneAddress,' ') ) B
Cross Apply (Select * from @ISO where matchIdentifier like '%'+B.RetVal+'%') C),
cteSumm as (
Select TransID,ID,RowNr=Row_Number() over (Partition By TransID Order by Count(*) Desc)
From cteBase
Group By TransID,ID
)
Select B.*,C.*
From cteSumm A
Join @YouTable B on (A.RowNr=1 and A.TransID = B.TransID)
Join @ISO C on (A.RowNr=1 and A.ID=C.ID)
Возвращает
UDF
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John||Cappelletti||was||here','||')