2015-06-14
317
Подобно тому, как происходит возвращение совпадений, из каждого совпадения можно извлекать данные. Использовать для этого SQL довольно затруднительно. Обычно этот вид задач выполняется приложениями, а не средствами базы данных, что вызывает определенные проблемы, так как каждое приложение, использующее базу данных, должно выполнять соответствующую обработку данных. При таких сценариях разумным является подход, позволяющий добиться требуемой функциональности с помощью сохраненных процедур.
Как и с реализацией функции RegexMatches, я предпочитаю использовать пользовательский счетный объект, возвращающий групповую информацию. Группировка представляет собой чуть более сложную процедуру, поскольку в пределах каждого совпадения должны выполняться итерации по группам. На рис. 4 класс GroupNode выглядит, практически, как класс MatchNode, за исключением того, что он также включает имя группы, которую представляет. Класс GroupIterator аналогичен классу MatchIterator, но включает в себя дополнительный цикл для возвращения к каждой группе. Теперь, когда у нас есть счетный объект, можно точно так же определить табличную функцию, как это было сделано для функции RegexMatches.
Figure 4 Пользовательский счетный объект для групп
internal class GroupNode
{
private int _index;
public int Index { get { return _index; } }
private string _name;
public string Name { get { return _name; } }
private string _value;
public string Value { get { return _value; } }
public GroupNode(int index, string group, string value)
{
_index = index;
_name = group;
_value = value;
}
}
internal class GroupIterator: IEnumerable
{
private Regex _regex;
private string _input;
public GroupIterator(string input, string pattern)
{
_regex = new Regex(pattern, UserDefinedFunctions.Options);
_input = input;
}
public IEnumerator GetEnumerator()
{
int index = 0;
Match current = null;
string[] names = _regex.GetGroupNames();
do
{
index++;
current = (current == null)?
_regex.Match(_input): current.NextMatch();
if (current.Success)
{
foreach(string name in names)
{
Group group = current.Groups[name];
if (group.Success)
{
yield return new GroupNode(
index, name, group.Value);
}
}
}
}
while(current.Success);
}
}
На рис. 5 функция RegexGroups определяется аналогично функции RegexMatches, за исключением того, что она возвращает дополнительный столбец данных, содержащий имя группы внутри совпадения. Теперь с помощью этой функции мы можем находить в пределах строки различные совпадения и извлекать из каждого совпадения конкретные фрагменты информации.
Figure 5 Табличная функция CLR UDF для групп
[SqlFunction(FillRowMethodName = "FillGroupRow", TableDefinition =
"[Index] int,[Group] nvarchar(max),[Text] nvarchar(max)")]
public static IEnumerable
RegexGroups(SqlChars input, SqlString pattern)
{
return new GroupIterator(new string(input.Value), pattern.Value);
}
[SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters")]
public static void FillGroupRow(object data,
out SqlInt32 index, out SqlChars group, out SqlChars text)
{
GroupNode node = (GroupNode)data;
index = new SqlInt32(node.Index);
group = new SqlChars(node.Name.ToCharArray());
text = new SqlChars(node.Value.ToCharArray());
}
При работе с базами данных импортирование данных в различных форматах является распространенной задачей. Импортирование данных в файлы с форматом, где разделителями являются запятые, представляет собой значительно более рутинное дело, чем следовало бы. Большинство разработчиков создают приложения, которые обрабатывают каждую строку, извлекают из нее данные и затем для каждой строки запускают сохраненную процедуру. И хотя этот процесс нормально работает, я бы предложил другое решение. Что если бы мы могли передать весь файл сохраненной процедуре и позволить ей управлять всем процессом? Эта идея обычно рассматривается как слишком сложная для реализации, но благодаря функции RegexGroups можно осуществить этот ввод фактически с помощью единственного запроса. Рассмотрим, к примеру, следующие данные о заказчиках.
2309478,Janet Leverling,J
2039748,Nancy Davolio,N
0798124,Andrew Fuller,M
4027392,Robert King,L
Из каждой строки необходимо получить три различных представляющих интерес фрагмента информации: номер заказчика, состоящий из семи цифр, фамилию заказчика и тип заказчика, определяемый одной буквой. Все три фрагмента информации можно извлечь с помощью следующего выражения:
(?<CustomerNumber>\d{7}),(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n
В этой ситуации остается проблема, заключающаяся в том, что результаты, возвращенные функцией RegexGroups, не могут быть прямо использованы. Вместо курсора для итерации результатов можно использовать основные функциональные возможности SQL Server 2005. Поместив все это вместе в сохраненную процедуру, мы получим все, что нам необходимо. Сохраненная процедура, приведенная на рис. 6, может принять целый текстовый файл с разделителем-запятой, содержащий до 2 Гб данных в формате Unicode. Она обрабатывает весь файл целиком и каждую строку этого файла вставляет в таблицу Customer. Таким же образом может быть обработан любой текстовый файл с разделителями. В шаблон могут быть внесены некоторые несущественные изменения и добавлены управляющие последовательности для поддержки запятых в строках.
Figure 6 Обработка файла с запятыми в качестве разделителя
create proc ImportCustomers
(
@file nvarchar(max)
)
as
declare @pattern nvarchar(max)
set @pattern = N'(?<CustomerNumber>\d{7}),
(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n'
insert [Customer]
(
[CustomerNumber],
[CustomerName],
[CustomerType]
)
select
f.[CustomerNumber],
f.[CustomerName],
f.[CustomerType]
from dbo.RegExGroups(@file, @pattern) regex
pivot
(
max([Text])
for [Group]
in ([CustomerNumber], [CustomerName], [CustomerType])
) as f
Эта процедура также демонстрирует существование различных путей решения одной и той же задачи, причем не всегда регулярные выражения являются наилучшим выбором. В приведенном примере использование основных функциональных возможностей означает фактический отказ от всей работы, которую выполняла функция RegexGroups для возвращения данных в специальном групповом формате. Можно ввести данные напрямую в таблицу, использующую гораздо более простую и быструю табличную функцию (table-valued function, TVF), просто считывающую каждую строку, применить метод String.Split на запятых и возвратить каждую строку в обработанном виде.
