Продолжим рассмотрение функций семейства REGEXP_, следующая, на которой я хочу остановиться, — это табличная функция REGEXP_SPLIT_TO_TABLE. Это определённо одна из тех функций, которые стоит знать, особенно если когда-либо потребуется извлекать данные из сложной структуры.
Эта функция очень похожа на STRING_SPLIT. И, в отличие от таких функций, как REGEXP_LIKE, в простых случаях здесь можно использовать те же основные параметры, что и у STRING_SPLIT. Но далее возможности становятся практически безграничными, потому что можно определить почти любые разделители. Конечно, у функции есть и недостатки, но об этом поговорим позже.
Немного предыстории о STRING_SPLIT
Функция STRING_SPLIT принимает три параметра: строку для разбиения, разделитель (строго один символ) и необязательный параметр для добавления порядкового номера строк результата. Например:
SELECT *
FROM STRING_SPLIT('a,b,c,d,e',',');
Результат:
value
---------
a
b
c
d
e
SELECT *
FROM STRING_SPLIT('a,b,c,d,e','c',1);
В этом случае всё, что было до символа c, помещается в первую строку, всё, что после — во вторую. Появляется колонка ordinal, по которой безопасно сортировать:
value ordinal
--------- --------------------
a,b, 1
,d,e 2
Главное ограничение STRING_SPLIT — использование только одного символа в качестве разделителя. Нельзя задать разделитель «c,» или, например, перевод строки. Попытка передать более одного символа вызовет ошибку времени выполнения:
value
---------
Msg 214, Level 16, State 11, Line 19
Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'.
Эта ошибка указывает на проблему выполнения функции, а не на синтаксическую ошибку. Это связано с тем, что разделитель можно передать как переменную, и проверка типа выполняется только при работе функции. Именно на этом месте STRING_SPLIT упирается в серьёзное ограничение. Поэтому я использую её редко, особенно с более сложными данными.
REGEXP_SPLIT_TO_TABLE
И вот здесь на помощь приходит более универсальная функция REGEXP_SPLIT_TO_TABLE. С её помощью можно разбирать строки или файлы по любым разделителям, включая регулярные выражения.
Эта функция работает противоположно REGEXP_MATCHES: та возвращает совпавшие символы, а REGEXP_SPLIT_TO_TABLE возвращает то, что не совпало. Использовать обе функции вместе бывает очень полезно.
Возьмём наш предыдущий пример:
SELECT *
FROM REGEXP_SPLIT_TO_TABLE('a,b,c,d,e',',');
Результат аналогичен STRING_SPLIT, но колонка ordinal теперь присутствует всегда:
value ordinal
--------- --------------------
a 1
b 2
c 3
d 4
e 5
Если заменить на REGEXP_MATCHES, то мы увидим разделитель 4 раза:
SELECT match_id, match_value
FROM REGEXP_MATCHES('a,b,c,d,e',',');
match_id match_value
-------------------- -----------
1 ,
2 ,
3 ,
4 ,
Пустые разделители
Если разделители идут подряд без промежутков, то возвращаются пустые строки:
SELECT *,LEN(Value) AS LENValue
FROM REGEXP_SPLIT_TO_TABLE('a,b,,,c,d,,e',',');
value ordinal LENValue
------------ -------------------- -----------
a 1 1
b 2 1
3 0
4 0
c 5 1
d 6 1
7 0
e 8 1
Пустые значения можно отфильтровать или заменить на NULL. В более сложных случаях такие ситуации возникают часто.
Расширенные разделители
Теперь примеры сложнее. Можно использовать сразу несколько символов в регулярном выражении как разделители:
SELECT *
FROM REGEXP_SPLIT_TO_TABLE('a,b|c$d!e','[,$!|]');
value ordinal
--------- --------------------
a 1
b 2
c 3
d 4
e 5
Или, например, разделитель — перевод строки:
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'a
b
c
d
e','\r\n');
value ordinal
------------- --------------------
a 1
b 2
c 3
d 4
e 5
Можно комбинировать разделители через оператор | (ИЛИ). Порядок при этом имеет значение: сначала сложные выражения, потом простые.
Другие примеры
Разбиение по пробелам:
SELECT *
FROM REGEXP_SPLIT_TO_TABLE('a b c d e','\s');
Разбиение по нескольким пробелам или переводам строки:
SELECT *,LEN(Value) AS LENValue
FROM REGEXP_SPLIT_TO_TABLE(
'a b
c d
e','\s+');
value ordinal LENValue
---------------- -------------------- -----------
a 1 1
b 2 1
c 3 1
d 4 1
e 5 1
Пример с CSV, где значения заключены в кавычки:
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'1,"aa","bb",2,3,"cc"', '","|,"|",|,|"');
value ordinal
-------------------- --------
1 1
aa 2
bb 3
2 4
3 5
cc 6
Ограничения
Главное ограничение — невозможность использовать так называемые lookahead (заглядывания вперёд). Регулярные выражения в SQL Server обрабатываются символ за символом, без возможности проверять следующий символ. Это значит, что если разделитель встречается внутри значения, корректно обработать строку не получится.
Последний приём
Можно использовать пустую строку как разделитель, чтобы разбить строку на отдельные символы:
SELECT *, UNICODE(CAST(VALUE AS NCHAR(1))) AS UNICODEVALUE
FROM REGEXP_SPLIT_TO_TABLE('abc''d
ef','');
value ordinal UNICODEVALUE
--------- -------------------- -----------
a 1 97
b 2 98
c 3 99
' 4 39
d 5 100
6 13
7 10
e 8 101
f 9 102
Это полезно при анализе «грязных» данных, где встречаются неожиданные символы. Можно быстро выявить лишние знаки, пробелы или управляющие символы.
Заключение
В этой статье мы рассмотрели разбиение строк с помощью REGEXP_SPLIT_TO_TABLE и STRING_SPLIT. Как обычно, эти функции можно применять и к данным в таблицах с помощью CROSS APPLY или OUTER APPLY.
STRING_SPLIT хорошо подходит для простых сценариев, например, разбора параметров. Но REGEXP_SPLIT_TO_TABLE (несмотря на длинное и неудобное название) оказывается незаменимым инструментом при работе со сложным текстом — при условии, что разделители не встречаются внутри самих данных.
На практике это часто бывает сложнее, чем кажется, и иногда проще заменить разделители в исходных данных на другие, чтобы гарантировать корректность обработки.
Комментариев нет:
Отправить комментарий