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

Откуда: Краснодар
Сообщений: 1015
+ Предупреждение

Все, что тут написано - используйте на свой страх и риск. Автор ответственности не несет.


+ На кого рассчитано


Для всех, кому надоело писать substring(right(charindex(......)...)...) o_O.

Предполагается знание regex'ов или наличие желания их изучить.



+ Что почитать


Jeffrey E. F. Friedl - Mastering Regular Expressions


Jan Goyvaerts - Regular Expressions Cookbook


При желании все гуглится.


+ Инструменты для отладки


Online:

regex101
regexpal
RegexBuddy



+ Как включить хостинг CLR


Подробности тут

sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'clr enabled', 1;
go
reconfigure;
go



+ Настройка поддержки Regex в БД


Просто в контексте нужной базы выполните скрипт из приложения regex_install.sql

То что в скрипте, скомпилено в Release конфигурации под .NET Framework 3.5.
Тестировалось на SQL Server 2014, на остальных тоже должно работать. Под рукой нет проверить.

Также в приложении лежит проект с исходниками. Допиливайте под себя, как хотите.




Нужно распарсить текст и получить данные в виде таблицы? Попробуйте Regex_Matches.

+ ext.Regex_Matches


Поскольку это всего лишь обертка статических методов класса Regex из состава .NET Framework, то актуальна
документация

CREATE FUNCTION [ext].[Regex_Matches]
(
	@input NVARCHAR (MAX), 
	@pattern NVARCHAR (4000), 
	@options NVARCHAR (4000)
)
RETURNS 
	TABLE 
	(
		[match]    INT            NULL,
		[group]    INT            NULL,
		[capture]  INT            NULL,
		[position] INT            NULL,
		[length]   INT            NULL,
		[value]    NVARCHAR (MAX) NULL
	)
AS EXTERNAL NAME [RegexWrapper].[UserDefinedFunctions].[Regex_Matches]
GO


Обертка реализована как table valued function. Параметры по названию и смыслу полностью совпадают с
соответствующим статическим методом .NET Regex.Matches. Возвращает в виде таблицы куски текста @input, которые
удовлетворяют регулярному выражению @pattern.

Как формируется возвращаемая функцией таблица совпадений. Под капотом вызывается статический метод Regex.Matches,
который возвращает коллекцию MatchCollection
эта коллекция сделана "ленивой". Т. е. регулярное выражение применяется не целиком ко всему тексту сразу, а по мере того, как перебираются элементы
этой коллекции. Этот принцип постарался сохранить при возврате строк SQL Server'у (с помощью итераторов), чтобы регулярное выражение применялось к
тексту по мере получения строк из функции (сделано, например для TOP(n), в этом случае регулярное выражение будет применено не более N раз).

Каждый элемент коллекции MatchCollection представляет собой объект класса ]Match,
каждый из которых, в свою очередь, может иметь коллекцию GroupCollection, состоящую из
объектов класса Group, каждый из которых, в свою очередь, может иметь
коллекцию CaptureCollection, состоящую из объектов класса Capture

За смыслом классов Match/Group/Capture обращайтесь к документации по ссылкам выше.

В возвращаемой таблице колонка:
- match соответствует номеру объекта Match в коллекции MatchCollection
- group соответствует номеру объекта Group в коллекции match.Groups
- capture соответствует порядковому номеру объекта capture в коллекции group.Captures
- value найденная подстрока
- position позиция первого символа подстроки value
- length длина подстроки value

Примеры использования:

/*
	Вытащить из текста все, что похоже на email.
*/
declare
	@d1 nvarchar(max) =
	N'
	he38c4 3vcjh3hcn4c8 hbfjf wejhbweyx2b hy&^TVIU G23dy2b3uhs BJJ
		email1@domain1.ru wjechne nx32y38
		djvnsdk
	28cb839&*%*V&^UBJHKLLMv email2@domain2.ru cnhwcy3287ry38r 


	email3@l3.l2.l1.local. 3uiycbr43ury34ius iu3yb
	ferfkje
			oierjg34gh34g
	';

select *
from
	ext.Regex_Matches(@d1, N'\w+@(?:\w+|\.)+\w+', NULL);


matchgroupcapturepositionlengthvalue
1116917email1@domain1.ru
21114017email2@domain2.ru
31118121email3@l3.l2.l1.local


/*
	Вытащить из текста пары param_name = value
*/
declare
	@d2 nvarchar(max) =
	N'
		ubyc237  32423v4 &*(YB&TON(*UPMOKKLe param1 = value1
			fw4g34v34tv34vt34fw wc487ry943r78 w kjf
				ij param2 = value2

			cwoejwioeh fw param3=
			value3 ejfhwekfuiwe82f93
	';

/*
	Для наглядности сначала просто распарсим, чтобы видно было, как
	MatchCollection ложится в таблицу.

	Здесь специально применяются группы (...)(...), чтобы
	увидеть все это в таблице.
	
	group = 1 это весь кусок текста соответствую регулярке
	group = 2 это первая (...) название параметра
	group = 3 это вторая (...) значение параметра
*/
select *
from
	ext.Regex_Matches(@d2, N'(\w+)\s*=\s*(\w+)', NULL);



matchgroupcapturepositionlengthvalue
1114115param1 = value1
121416param1
131506value1
21110915param2 = value2
2211096param2
2311186value2
31114518param3= value3
3211456param3
3311576value3



/*
	Вывернем в строки
*/

with t0 as
(
	select
		[match],
		[group],
		value
	from
		ext.Regex_Matches(@d2, N'(\w+)\s*=\s*(\w+)', NULL)
)
select
	pvt.match,
	pvt.[2] as [param_name],
	pvt.[3] as [param_value]
from
	t0
		pivot
	(max(value) for [group] in ([2], [3])) pvt


matchparam_nameparam_value
1param1value1
2param2value2
3param3value3


24 фев 15, 23:27    [17307786]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Скрипт заливает сборку (SAFE) и создает ссылки на функции в сборке.

К сообщению приложен файл (regex_install.sql - 56Kb) cкачать
24 фев 15, 23:29    [17307789]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
o-o
Guest
спасибо

К сообщению приложен файл. Размер - 181bytes
24 фев 15, 23:29    [17307790]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Проект с исходниками.

К сообщению приложен файл (RegexWrapper.zip - 8Kb) cкачать
24 фев 15, 23:30    [17307794]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Позже опишу, как будет время:

ext.Regex_Replace
ext.Regex_Split
ext.Regex_IsMatch

в сборке это все есть, если что, можно разобраться самостоятельно по доке из .NET Framework. Сигнатуры те же. Также опишу для всех функций параметр @options... (или по коду пока гляньте, что туда можно пихать).
24 фев 15, 23:35    [17307803]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31602
churupaha,

Спасибо!
25 фев 15, 00:47    [17307977]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Публиковать ваш материал в виде блога на этом ресурсе не пробовали?
C FAQ пока все сложно.
25 фев 15, 02:07    [17308113]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4378
churupaha, спасибо!
Толково и по существу.
25 фев 15, 09:14    [17308412]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Извините, не понял, в чём прикол.
Вы сделали обёртку для основных методов из пространства имён System.Text.RegularExpressions ?
И что здесь особенного?
25 фев 15, 09:29    [17308453]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4378
Jovanny
Извините, не понял, в чём прикол.
Вы сделали обёртку для основных методов из пространства имён System.Text.RegularExpressions ?
И что здесь особенного?

Особенного ничего. Но это опубликовано в виде готового решения. Отсюда и FAQ.
Не сомневаюсь, что каждый, кто немного разбирается в теме, при необходимости, может легко это сделать.
НО... это уже сделано. Не нужно искать, читать и разбираться.
Экономит время. За что и спасибо.
25 фев 15, 09:37    [17308484]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
SQL2008,

Я предпочитаю видеть как T-SQL код, так и .Net код, с которым работаю. А чего ТС напихал в свои сборки, неизвестно. Не исключено, что и какой-нибудь вредоносный код.
Так что мой совет - пишите свои CLR сборки. И попутно изучайте какой-нибудь .Net язык, потому что требования к базам данных возрастают с каждым днём и одним Transact-SQL уже не обойтись..
25 фев 15, 09:47    [17308515]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Хотите разбить текст на куски по разделителю? Попробуйте ext.Regex_Split

+ ext.Regex_Split


Поскольку это всего лишь обертка статических методов класса Regex из состава .NET Framework, то актуальна документация.

CREATE FUNCTION [ext].[Regex_Split]
(
	@input [NVARCHAR](max), 
	@pattern [NVARCHAR](4000), 
	@options [NVARCHAR](4000)
)
RETURNS  
	TABLE 
	(
		[part] [NVARCHAR](max) NULL
	)
AS EXTERNAL NAME [RegexWrapper].[UserDefinedFunctions].[Regex_Split]


Здесь @pattern задает шаблон разделителя. Строка @input бьетсяна куски по разделителю @pattern.

Возвращается таблица строк (кусков, на которые была разбита исходная строка).

/*
	Случай с константным разделителем. Разбить строку по разделителю ','.
*/
declare
	@d nvarchar(max) = N'1,2,3,4,5,6,7,8';

select * from ext.Regex_Split(@d, N',', NULL);


part
1
2
3
4
5
6
7
8


/*
	Случай с переменным разделителем. Разбить строку на числа.
*/
declare
	@d nvarchar(max) = N'1vV%&R^TG2jh32re4c44vc5v54b56y,tyhtyhtyh6,7&*(*#&*TJH7gg8';

select * from ext.Regex_Split(@d, N'[^\d]+', NULL);


part
1
2
32
4
44
5
54
56
6
7
7
8


/*
	Случай с переменным разделителем по некоторому шаблону. Разбить строку на числа.
	Здесь разделителем будет все что задано в виде #XY.ZQ...#, где X, Y, Z, Q... - произвольные цифры
*/
declare
	@d nvarchar(max) = N'erhbfrubfwu23$%^E$E&*&e#11.23333444#EJKRHVEGHREYEHJbjhv76t7^R&#17.918789687#V^%R&%ETFGHFff#57.7867#bfjh';

select * from ext.Regex_Split(@d, N'#\d{2}\.\d{2,}#', NULL);


part
erhbfrubfwu23$%^E$E&*&e
EJKRHVEGHREYEHJbjhv76t7^R&
V^%R&%ETFGHFff
bfjh


25 фев 15, 09:47    [17308516]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Хотите что-то заменить в строке? Попробуйте ext.Regex_Replace.

+ ext.Regex_Replace

Поскольку это всего лишь обертка статических методов класса Regex из состава .NET Framework, то актуальна документация.

CREATE FUNCTION [ext].[Regex_Replace]
(
	@input [NVARCHAR](max), 
	@pattern [NVARCHAR](4000), 
	@replacement [NVARCHAR](max), 
	@options [NVARCHAR](4000))
RETURNS 
	[nvarchar](max)
AS 
EXTERNAL NAME [RegexWrapper].[UserDefinedFunctions].[Regex_Replace]


Обертка очень простая. Сделана в виде скалярной функции. Ищет в строке @input подстроки,
соответствующие шаблону @pattern и заменяет их на строку @replacement. Причем, в @replacement
есть возможность ссылаться на подстроки в заменяемой строке (пример ниже).

/*
	Попытка вычистить грязную строку под CSV с разделителем ';'
*/
declare
	@d nvarchar(max) = 
	N'
	534,	34^^%&, 35,&% "ООО Рога и копыта",765857
	';

select ext.Regex_Replace(@d, N'[^"\w]*,[^"\w]*', N';', NULL) as res;


res
534;34;35;"ООО Рога и копыта";765857


/*
	Демонстрация возможности ссылок из ЗАМЕНЯЮЩЕЙ строки на куски ЗАМЕНЯЕМОЙ. 
*/
with t0(url) as
(
	select 'http://www.mydomain1.ru/c/pictures/0001.jpg' union all
	select 'ftp://zzz2.ru/d/pictures/test02.png' union all
	select 'hz://www1.www2.mydomain1.ru/e/pictures/pict_0017.jpg'
)
/*
	Специально в шаблоне выделяются группы, на которые будем ссылаться при замене.
	Нумеруются в порядке открывающих скобок. Шаблон выглядит как (..1..)(?:...)(..2..)(..3..),
	та группа что с ?: не учавствует в захвате текста. Соответственно из replacement можно на группы ссылаться
	по $1, $2, $3. Подробнее об этом можно почитать в книжках по regex'ам.
*/
select ext.Regex_Replace(url, N'(\w+)\:\/\/(?:.*?)/(\w)/(.*)', N'Файл: $2:/$3 доступен по протоколу: $1 ', NULL) as res
from
	t0;


res
Файл: c:/pictures/0001.jpg доступен по протоколу: http
Файл: d:/pictures/test02.png доступен по протоколу: ftp
Файл: e:/pictures/pict_0017.jpg доступен по протоколу: hz


25 фев 15, 10:48    [17308762]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
churupaha,

Рекламируете своё произведение?)
25 фев 15, 10:58    [17308832]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
o-o
Guest
Jovanny
churupaha,
Рекламируете своё произведение?)

+

кому завидно, может свое выложить.
как будто мало других тем "оцените мой велосипед".
но вот лично мне, чтобы за что-то взяться, надо сперва увидеть, что оно работает.
а когда для того, чтобы увидеть, вернее, еще "до того", надо кучу всего прочесть, руки опускаются.
и как раз с левого ресурса я себе ничего грузить не стану, а кто считает
"чего ТС напихал в свои сборки, неизвестно. Не исключено, что и какой-нибудь вредоносный код"
может и не грузить, и правильно считает, разве же кто против, просто не для вас тема, всего-то.
или уже и спасибо запрещено здесь говорить?
мне, чтобы посмотреть в сторону CLR, нужен был хороший пинок, но меня никак не припирало.
а теперь займусь.

может оно и правда лучше бы выглядело в виде блога,
но пойди догадайся, что еще не работает на этом ресурсе, кроме фака.
одно ясно: в виде темы публиковать можно, вот человек и публикует
25 фев 15, 11:22    [17309005]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Jovanny
А чего ТС напихал в свои сборки, неизвестно. Не исключено, что и какой-нибудь вредоносный код.

Выше дана ссылка на исходники.
Тема называется "FAQ: Regex...". Как и в любом FAQ, тут нет ничего выдающегося, т.к. задача FAQ не в этом, а в том, чтобы быстро найти ответ, на часто задаваемый вопрос или решение популярной проблемы, вместо того, чтобы разбираться самому и попутно мучить других участников форума избитой темой.
Рекламы никакой нет - есть примеры. Если все хорошо протестировано и работает без ошибок - очень полезная тема, по этому, пожалуйста, не засоряйте ее умозрительными комментариями. Если есть желание высказаться, можно сделать конструктивный вклад, например: найти ошибку/неточность/сделать тесты производительности/рассмотреть альтернативные способы решения и т.д. Оформить и выложить как дополнение.
25 фев 15, 11:23    [17309016]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Хотите проверить удовлетворяет ли строка шаблону? Попробуйте ext.Regex_IsMatch.

+ ext.Regex_IsMatch

Поскольку это всего лишь обертка статических методов класса Regex из состава .NET Framework, то актуальна документация.

CREATE FUNCTION [ext].[Regex_IsMatch]
(
	@input [NVARCHAR](max), 
	@pattern [NVARCHAR](4000), 
	@options [NVARCHAR](4000)
)
RETURNS
	[bit]
AS EXTERNAL NAME [RegexWrapper].[UserDefinedFunctions].[Regex_IsMatch]


Обертка очень простая. Скалярная функция. Если строка @input удовлетворяет шаблону
@pattern, то возвращает 1, иначе 0.

with t0(email) as
(
	select 'e1@d1.ru' union all
	select 'e2_zzz@d3.d2.d1.ru' union all
	select 'e3@d3_dsds_d2.d1.d4.ru' union all
	select '^%R^&RGHJ'
)
select
	email,
	-- Я просто скопипастил упрощенный RFC2822
	ext.Regex_IsMatch(email, '[a-z0-9!#$%&''*+/=?^_''{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_''{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?', NULL) as is_valid
from
	t0;


emailis_valid
e1@d1.ru1
e2_zzz@d3.d2.d1.ru1
e3@d3_dsds_d2.d1.d4.ru0
^%R^&RGHJ0


/*
    Regex в check constraint'ах
*/

create table t(id int, email nvarchar(max));
go

alter table t add constraint chk_t_email
check
(
        -- Скопипастил чью-то попытку описать RFC 2822
	1 = ext.Regex_IsMatch(email, N'(?:[a-z0-9!#$%&''*+/=?^_''{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_''{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])', N'Singleline')
);
go

insert into t(id, email) values(1, 'myemail@d4.d2.d3.ru');
go
insert into t(id, email) values(1, 'defel2@zzz.ru');
go
insert into t(id, email) values(1, 'не email');
go


вывод

(1 row(s) affected)

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the CHECK constraint "chk_t_email". The conflict occurred in database "zzz1", table "dbo.t", column 'email'.
The statement has been terminated.


25 фев 15, 11:28    [17309049]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
У каждой обертки есть параметр @options. Что туда передавать?

+ Regex_XXX(..., @options)


У всех исходных обернутых функций .NET Framework он тоже есть. Но в .NET это enum с флажками RegexOptions.

В .NET задание опций выглядело бы так:

Regex.Split(..., RegexOptions.IgnoreCase | RegexOptions.Singleline);

При использовании обертки, передавайте в @options строку:

ext.Regex_Split(..., N'IgnoreCase | Singleline | IgnorePatternWhitespace')

допустимо
ext.Regex_Split(..., N'IgnoreCase		| 
Singleline |			IgnorePatternWhitespace')


Если устраивают поведение по умолчанию, передавать можно NULL

25 фев 15, 11:41    [17309133]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Вроде все. Всем пожалуйста, надеюсь это кому-то сэкономит время (мне сильно экономило).
25 фев 15, 11:51    [17309237]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Гавриленко Сергей Алексеевич,

я как-то хотел сделать тут блог. подал заявку и глухо. давно было. с FAQ тоже самое.
25 фев 15, 12:00    [17309303]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Если кто-то заморочится изучением regex'ов, то это не пропадет даром. Это палка выручалка на каждый день. Ее поддерживаеют многие от всяких grep'ов/perl'ов/java и редакторов до некоторых других СУБД, сами знаете каких (прямо из коробки)
25 фев 15, 12:11    [17309364]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Последние исходники будут доступны на GitHub:

RegexWrapper Repository

Если кто найдет ошибки, правьте и делайте свои commit'ы.
5 мар 15, 19:50    [17349189]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
b00ring
Member

Откуда:
Сообщений: 59
churupaha, я правильно понимаю, без компиляции проекта сборку не обновить?

Можно обнаглеть и попросить вас обновить regex_install.sql ?

Спасибо.
21 май 15, 23:12    [17673851]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Если стоит предыдущая версия, то для обновления использовать этот скрипт

К сообщению приложен файл (regex_update-22.05.2015.sql - 59Kb) cкачать
21 май 15, 23:50    [17673930]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Если устанавливать в новую базу, то использовать этот скрипт

К сообщению приложен файл (regex_install-22.05.2015.sql - 59Kb) cкачать
21 май 15, 23:52    [17673934]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Изменения:

- Добавлена скалярная функция dbo.Regex_Match
- В существующую табличную функцию dbo.Regex_Matches в результирующий набор добавлена колонка group_name (при использовании regex named groups там будет имя группы)
- Пофиксена какая-то мелочь...
21 май 15, 23:55    [17673944]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Пардон. Предыдущие скрипты:

regex_update-22.05.2015.sql
regex_install-22.05.2015.sql

содержат Debug сборку.

Поверх нужно накатывать этот скрипт, в нем Release сборка.

К сообщению приложен файл (regex_fix-22.05.2015.sql - 56Kb) cкачать
22 май 15, 00:24    [17673987]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Использование Named Capturing Groups в ext.Regex_Matches

+ Named Capturing Groups


Подробнее о Named capturing Groups можно прочитать тут

В ext.Regex_Matches добавлена колонка group_name. В этой колонке содержится имя группы, которой соответствует значение в колонке value текущей строки.

with 
t0(id, d) as
(
    select 1, N'house==155||postal==600025||city==Владимир||street==Октябрьская||' union all
    select 2, N'postal==773||city==Певек||street==Красная||house==3423||' union all
    select 3, N'city==Владивосток||postal==748||street==Мира||house==554||' union all
    select 4, N'street==Октябрьская||postal==858||city==Казань||house==443||'
)
select
    id, group_name, value
from
    t0
        cross apply
     ext.Regex_Matches
     (
         d,
         -- здесь значения параметров выделены в именованные группы (?<group_name>[^|]*)
	 N'postal==(?<postal>[^|]*)|city==(?<city>[^|]*)|street==(?<street>[^|]*)|house==(?<house>[^|]*)',
	 NULL
     ) m




id group_name value
----------- ------------------------------ ------------------------------
1 0 house==155
1 house 155
1 0 postal==600025
1 postal 600025
1 0 city==Владимир
1 city Владимир
1 0 street==Октябрьская
1 street Октябрьская
2 0 postal==773
2 postal 773
2 0 city==Певек
2 city Певек
2 0 street==Красная
2 street Красная
2 0 house==3423
2 house 3423
3 0 city==Владивосток
3 city Владивосток
3 0 postal==748
3 postal 748
3 0 street==Мира
3 street Мира
3 0 house==554
3 house 554
4 0 street==Октябрьская
4 street Октябрьская
4 0 postal==858
4 postal 858
4 0 city==Казань
4 city Казань
4 0 house==443
4 house 443

Используя pivot можно вывернуть эти данные в строки:

with 
t0(id, d) as
(
    select 1, N'house==155||postal==600025||city==Владимир||street==Октябрьская||' union all
    select 2, N'postal==773||city==Певек||street==Красная||house==3423||' union all
    select 3, N'city==Владивосток||postal==748||street==Мира||house==554||' union all
    select 4, N'street==Октябрьская||postal==858||city==Казань||house==443||'
),
t1 as
(
    select
        id, group_name as [col_name], value
    from
        t0
            cross apply
	ext.Regex_Matches
	(
	    d,
            -- здесь значения параметров выделены в именованные группы (?<group_name>[^|]*)
	    N'postal==(?<postal>[^|]*)|city==(?<city>[^|]*)|street==(?<street>[^|]*)|house==(?<house>[^|]*)',
            NULL
	) m
)
select *
from
    t1
        pivot
    (max(value) for [col_name] in (city, street, house, postal)) p;




id city street house postal
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 Владимир Октябрьская 155 600025
2 Певек Красная 3423 773
3 Владивосток Мира 554 748
4 Казань Октябрьская 443 858

(4 row(s) affected)




+ update обертки в приложении (мелкие исправления).

К сообщению приложен файл (regex-23.05.2015.zip - 17Kb) cкачать
22 май 15, 12:47    [17676179]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ ...
В этой версии была добавлена поддержка колонки group_name для Named Capturing Groups. Внутри обертки пришлось отказаться от вызова статического метода Regex.Matches и на каждый вызов пересоздавать Regex объект. Только имея экземпляр объекта Regex, можно получить доступ к названиям Capturing Groups. Публичные конструкторы класса Regex под капотом разбирают регулярку, создают промежуточный код, который потом интерпретируется. На это тратится время. Тоже самое делают и статические методы, НО под капотом они используют закрытый конструктор Regex, который имеет дополнительный параметр, указывающий нужно ли переиспользовать уже созданный ранее код регулярки.

https://msdn.microsoft.com/en-us/library/8zbs0h2f(v=vs.110).aspx
To improve performance, the regular expression engine maintains an application-wide cache of compiled regular expressions. The cache stores regular expression patterns that are used only in static method calls.


Рефлектор показывает тоже самое. Была мысль поступать также, но вызвать закрытый конструктор Regex, не потеряв в производительности не удалось (Reflection). Если у кого то есть мысли, исходники открыты на githab'е.


Потому код разделен на три ветки:

https://github.com/churupaha/RegexWrapper
SAFE сборка. Состояние кода соответствует этому посту 17676179
т. е. в ней сидит описанная выше проблема с пересозданием объектов (ext.Regex_Matches пересоздает объекты Regex
с нуля при каждом вызове обертки), но так как здесь
производительность кого-то устроила, было решено оставить этот вариант.


https://github.com/churupaha/RegexWrapper/tree/v1
SAFE сборка. Совместима со всем, что написано в этой теме, за исключением 17676179.
Поддержка колонки group_name нет. Обертка ext.Regex_Matches под капотом вызывает статический Regex.Matches. т. е. не содержит описанной выше проблемы.



https://github.com/churupaha/RegexWrapper/tree/v2
UNSAFE сборка. Тоже самое, что и ветка v1, но было добавлено новое API в схеме regex. Оно решает следующие проблемы:

1) Несмотря на то, что статические методы под капотом используют ранее сгенерированный код регулярки, сами объекты Regex создаются при каждом вызове. В закрытом конструкторе Regex устанавливаются ссылки на уже существующий в кеше код регулярки (проверено рефлектором). При обработке большого количества строк, это будет приводить к замусориванию .NET Heap объектами Regex, что будет приводить к частым срабатываниям GC.

2) Поддерживается колонка group_name, как pltcm 17676179, при этом нет постоянного пересоздания класса Regex.

3) Поддержка опции Compiled Regular Expressions.

https://msdn.microsoft.com/en-us/library/8zbs0h2f(v=vs.110).aspx
By default, the regular expression engine compiles a regular expression to a sequence of internal instructions (these are high-level codes that are different from Microsoft intermediate language, or MSIL). When the engine executes a regular expression, it interprets the internal codes.

If a Regex object is constructed with the RegexOptions.Compiled option, it compiles the regular expression to explicit MSIL code instead of high-level regular expression internal instructions. This allows the.NET Framework's just-in-time (JIT) compiler to convert the expression to native machine code for higher performance.

However, generated MSIL cannot be unloaded. The only way to unload code is to unload an entire application domain (that is, to unload all of your application's code.). Effectively, once a regular expression is compiled with the RegexOptions.Compiled option, the .NET Framework never releases the resources used by the compiled expression, even if the regular expression was created by a Regex object that is itself released to garbage collection.

You must be careful to limit the number of different regular expressions you compile with the RegexOptions.Compiled option to avoid consuming too many resources. If an application must use a large or unbounded number of regular expressions, each expression should be interpreted, not compiled. However, if a small number of regular expressions are used repeatedly, they should be compiled with RegexOptions.Compiled for better performance. An alternative is to use precompiled regular expressions. You can compile all of your expressions into a reusable DLL by using the CompileToAssembly method. This avoids the need to compile at runtime while still benefiting from the speed of compiled regular expressions.



Дополнительное API представлено следующими хранимками и функциями:

-- Создает объект Regex и возвращает его handle
regex.Alloc @pattern, @options, @handle output

-- Переиспользуют объект Regex
regex.Matches	@handle, @input
regex.Split	@handle, @input
regex.IsMatch	@handle, @input
regex.Match	@handle, @input
regex.Replace	@handle, @input, @replacement

-- Освобождает объект Regex, соответствующий handle'у
regex.Free @handle


т. е. объект Regex, представленный handle'ом, переиспользуется функциями. Передача некоего числа, описывающего
объект в .NET Heap в TSQL-код, да так, чтобы объект оставался на своем месте, а не был собран как мусор GC и прочее
между вызовами функций заставило сделать сборку UNSAFE.

О параллельных планах https://msdn.microsoft.com/en-us/library/6h453d2h(v=vs.110).aspx
The Regex class itself is thread safe and immutable (read-only). That is, Regex objects can be created on any thread and shared between threads; matching methods can be called from any thread and never alter any global state.


Итого, в этой ветке поддерживается два API в схемах:

1) ext - stateless API (которое было ранее)
2) regex - дополнительное statefull API



Пример адаптированного варианта 17676179 под дополнительное API

declare
	@rh bigint;

exec regex.Alloc
	-- здесь значения параметров выделены в именованные группы (?<group_name>[^|]*)
	@pattern = N'postal==(?<postal>[^|]*)|city==(?<city>[^|]*)|street==(?<street>[^|]*)|house==(?<house>[^|]*)', 
	@options = null, 
	@handle = @rh output;

with 
t0(id, d) as
(
    select 1, N'house==155||postal==600025||city==Владимир||street==Октябрьская||' union all
    select 2, N'postal==773||city==Певек||street==Красная||house==3423||' union all
    select 3, N'city==Владивосток||postal==748||street==Мира||house==554||' union all
    select 4, N'street==Октябрьская||postal==858||city==Казань||house==443||'
),
t1 as
(
    select
        id, group_name as [col_name], value
    from
        t0
            cross apply
	regex.Matches
	(
	    @rh,
	    d
	) m
)
select *
from
    t1
        pivot
    (max(value) for [col_name] in (city, street, house, postal)) p;

exec [regex].[Free] @handle = @rh;


Это дополнительное API сам толком не использовал, оно, экспериментальное. Может кому-то понадобится. Скрипты для установки будут позже.
17 июн 15, 23:29    [17785250]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ к предыдущему посту

К сообщению приложен файл. Размер - 11Kb
17 июн 15, 23:35    [17785280]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
В архиве код из одноименных веток см. 17785250

SAFE install-18.06.2015\safe\

regex-install-safe-master-18.06.2015.sql
regex-install-safe-v1-18.06.2015.sql
regex-uninstall.sql




UNSAFE install-18.06.2015\unsafe\

regex-install-unsafe-v2-18.06.2015.sql
RegexWrapperPublicKey.snk
regex-uninstall.sql




+ как установить UNSAFE сборку

Чтобы поставить UNSAFE. Правильнее всего - самим просмотреть код, сгенерировать свою пару ключей скомпилировать/подписать/раздать права/установить.


Но если это тестовый сервер для "поиграться", то можно использовать публичный ключ из архива RegexWrapperPublicKey.snk, соответствующим закрытым ключем подписана сборка.

1) раздать права, например, так

use master;
go

create asymmetric key __RegexWrapper from file = 'C:\temp\RegexWrapperPublicKey.snk';
go

create login __RegexWrapper from asymmetric key __RegexWrapper;
go

grant unsafe assembly to __RegexWrapper;
go


2) Выполнить regex-install-unsafe-v2-18.06.2015.sql из архива

Как будет время, выложу какие-нибудь тесты с Compiled Regular Expressions (когда в функцию regex.Alloc передается параметр @options = '... | Compiled | ...'.




К сообщению приложен файл (install-18.06.2015.zip - 32Kb) cкачать
18 июн 15, 09:30    [17785946]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Тест Compiled vs Interpreted Regex

regex_type        exec_count  avg_cpu_time_ms avg_elapsed_time_ms total_cpu_time_ms total_elapsed_time_ms
----------------- ----------- --------------- ------------------- ----------------- ---------------------
COMPILED REGEX 50 2584 2641 129234 132080
INTERPRETED REGEX 50 3388 3423 169437 171188

(2 row(s) affected)

Все сильно зависит от регулярки, данных, их объема и т. п..

+ Исходник теста (данные теста в архиве)

:setvar data_file "c:\temp\compiled_regex_test\data.txt"
:setvar compiled_res_file "c:\temp\compiled_regex_test\compiled_res.txt"
:setvar interpreted_res_file "c:\temp\compiled_regex_test\interpreted_res.txt"
:setvar executions "50"

/*

	Тестируем INTERPRETED REGEX, результаты выводим в файл $(interpreted_res_file)

*/
:out $(interpreted_res_file)
go

-- Загружаем данные. ~ 2Mb см. в приложении data.txt
declare
	@data nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(data_file)', single_nclob) as t);

declare
	@rh bigint,
	@dummy int,
	@cnt int;

-- создаем регулярку
exec regex.Alloc @pattern = N'\b(\w+((\r?\n)|,?\s))*\w+[.?:;!]', @options = N'Multiline', @handle = @rh output;

-- первый прогон
select @dummy = 1
from
	regex.Matches(@rh, @data) t;

-- $(executions) раз выполняем одно и то же регулярное выражение
set @cnt = $(executions);

while (@cnt > 0)
begin
	set statistics time on;

	select @dummy = 1
	from
		regex.Matches(@rh, @data) t;

	set statistics time off;

	set @cnt -= 1;
end;

exec regex.Free @rh;
go

/*

	Тестируем COMPILED REGEX, результаты выводим в файл $(compiled_res_file)

*/
:out $(compiled_res_file)
go

-- Загружаем данные. ~ 2Mb см. в приложении data.txt
declare
	@data nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(data_file)', single_nclob) as t);

declare
	@rh bigint,
	@dummy int,
	@cnt int;

-- создаем регулярку
exec regex.Alloc @pattern = N'\b(\w+((\r?\n)|,?\s))*\w+[.?:;!]', @options = N'Multiline | Compiled', @handle = @rh output;

-- первый прогон
select @dummy = 1
from
	regex.Matches(@rh, @data) t;

-- 50 раз выполняем одно и то же регулярное выражение
set @cnt = $(executions);

while (@cnt > 0)
begin
	set statistics time on;

	select @dummy = 1
	from
		regex.Matches(@rh, @data) t;

	set statistics time off;

	set @cnt -= 1;
end;

exec regex.Free @rh;
go

:out stdout
go

declare
	@interpretted_res nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(interpreted_res_file)', single_clob) as t),
	@compiled_res nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(compiled_res_file)', single_clob) as t);

with t0 as
(
	select
		d.regex_type,
		r.match, 
		r.[group], 
		cast(r.value as int) as value
	from
		(
			values
			(N'INTERPRETED REGEX', @interpretted_res),
			(N'COMPILED REGEX', @compiled_res)
		) as d(regex_type, res_data)
			cross apply
		ext.Regex_Matches(d.res_data,  N'SQL Server Execution Times:\s*CPU time = (\d+) ms,  elapsed time = (\d+) ms.', null) r
	where
		r.[group] > 0
)
select
	p.regex_type,
	count(1) exec_count,
	avg(p.[1]) as avg_cpu_time_ms, 
	avg(p.[2]) as avg_elapsed_time_ms,
	sum(p.[1]) as total_cpu_time_ms, 
	sum(p.[2]) as total_elapsed_time_ms
from
	t0
		pivot
	(max(value) for [group] in ([1], [2])) p
group by
	p.regex_type
go

18 июн 15, 13:10    [17787132]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
В качестве тестовых данных использовалось это, предварительно конвертированное в Unicode (~2Mb). На sql.ru не влазит (более 150 Kb).
18 июн 15, 13:15    [17787153]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
По производительности/оптимизации пара ссылок для полноты картины:

Best Practices for Regular Expressions in the .NET Framework


Optimizing Regular Expression Performance, Part I: Working with the Regex Class and Regex Objects

В последней ссылке приводятся замеры. Все что в этих ссылках приводится справедливо и для этой обертки.
18 июн 15, 13:23    [17787183]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
stut
Member

Откуда:
Сообщений: 491
Подскажите что например значит такой регекс -- (.*)(\\d+)(.*) --почему здесь цыфра(ы) обозначаются как \\d+ -- а не \d+. Что значит такач комбинация как \\ или больше -- или сами по себе они ничего не значат? И что вообще значат круглые скобки -- групировка -- в каком значение-- я например могу растолковать это выражение как група1--0 или больше любых символов--група2--0 или больше цыфр--група3--0 или больше любых символов.
1 окт 15, 20:30    [18223092]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
stut
Подскажите что например значит такой регекс -- (.*)(\\d+)(.*)


stut
почему здесь цыфра(ы) обозначаются как \\d+ -- а не \d+.


Что значит такач комбинация как \\ или больше -- или сами по себе они ничего не значат? И что вообще значат круглые скобки -- групировка -- в каком значение-- я например могу растолковать это выражение как група1--0 или больше любых символов--група2--0 или больше цыфр--група3--0 или больше любых символов.[/quot]

строка вида: g34f347fg374fweuyfw\ddddddd3874 соответствует регексу

группа1: g34f347fg374fweuyfw
группа2: \ddddddd
группа3: 3874
1 окт 15, 20:53    [18223199]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
stut
Подскажите что например значит такой регекс -- (.*)(\\d+)(.*)


если вы напишите такое в коде на T-SQL, то строка вида: g34f347fg374fweuyfw\ddddddd3874 будет соответствовать регексу, причем:

группа1: g34f347fg374fweuyfw
группа2: \ddddddd
группа3: 3874

stut
почему здесь цыфра(ы) обозначаются как \\d+ -- а не \d+.


откуда этот регекс? из когда на каком языке? там принято экранировать '\'? когда выдергиваете ее из кода - убирайте экранирующие слэши. и тогда смысл регулярки будет такой, который задумывал автор:

g34f347fg374fweuyfw\ddddddd3874вувувувувувуву

группа1: g34f347fg374fweuyfw\ddddddd387
группа2: 4
группа3: вувувувувувуву
1 окт 15, 20:58    [18223228]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
stut
Member

Откуда:
Сообщений: 491
churupaha,
Я имел ввиду Java -- ибо в этом контексте я изучал эту тему.
4 окт 15, 23:04    [18233953]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить