Используйте функции регулярных выражений в Fabric Data Warehouse

Применяется к:✅ аналитической конечной точке SQL и хранилищу данных в Microsoft Fabric

Функции регулярных выражений (regex) позволяют сопоставлять сложные текстовые паттерны и напрямую обрабатывать строковые данные в ваших запросах T-SQL. Используйте их для проверки форматов, извлечения значений из полуструктурированного текста, подсчёта появления шаблонов и очистки или маскировки данных, не перемещая их из хранилища.

Note

Регулярные выражения сейчас находятся в предварительной версии.

Хранилище и конечная точка SQL Analytics поддерживают скалярные функции регулярных выражений. Каждая из этих функций возвращает одно значение на каждую входную строку, поэтому их можно использовать в списке SELECT и выражениях WHERE, JOIN, GROUP BY, ORDER BY и CASE.

Note

В Warehouse доступны только скалярные функции регулярного выражения. Таблицные функции REGEXP_MATCHES и REGEXP_SPLIT_TO_TABLE не поддерживаются.

Поддерживаемые функции

Function Тип возвращаемого значения Description
REGEXP_LIKE Boolean Указывает, соответствует ли входный текст шаблону регулярного выражения. Используйте его как сказуемое, например, в WHERE клаузе или CASE выражении.
REGEXP_REPLACE Строковое выражение Возвращает исходную строку с заменой вхождений, соответствующих шаблону regex, строкой замены. Если совпадений не найдено, возвращается исходная строка.
REGEXP_SUBSTR Строковое выражение Возвращает n-е вхождение подстроки, соответствующей шаблону регулярного выражения.
REGEXP_INSTR int Возвращает начальную или конечную позицию совпадающей подстроки.
REGEXP_COUNT int Возвращает количество случаев, когда шаблон regex встречается в строке.

Syntax

REGEXP_LIKE ( string_expression, pattern_expression [ , flags ] )

REGEXP_REPLACE ( string_expression, pattern_expression [ , string_replacement [ , start [ , occurrence [ , flags ] ] ] ] )

REGEXP_SUBSTR ( string_expression, pattern_expression [ , start [ , occurrence [ , flags [ , group ] ] ] ] )

REGEXP_INSTR ( string_expression, pattern_expression [ , start [ , occurrence [ , return_option [ , flags [ , group ] ] ] ] ] )

REGEXP_COUNT ( string_expression, pattern_expression [ , start [ , flags ] ] )

Полное описание каждого аргумента см. в ссылке на справочник по функции в разделе «Поддерживаемые функции».

Флаги

Необязательный аргумент flags — это значение char или varchar длиной до 30 символов, которое задаёт соответствующие модификаторы. Если указаны противоречивые флаги, используется последний. Например, 'ic' приводит к совпадению, чувствительному к регистру.

Flag Description
c Сопоставление с учетом регистра. Это значение по умолчанию.
i Сопоставление без учета регистра.
m Многострочный режим: ^ и $ соответствуют началу и концу каждой строки, а также началу и концу текста.
s Давайте сопоставим . символ \nновой строки .

Рекомендации

  • Аргумент string_expression принимает строковые типы. Большие значения (varchar(max)) поддерживаются до 2 МБ.
  • Аргумент pattern_expression поддерживает максимальную длину 8 000 байт.
  • Функции регулярных выражений не учитывают правила сортировки. Сопоставление может отличаться от других сравнений строк, таких, как LIKE. Для контроля чувствительности регистра используйте флаги i и c вместо зависимости от колляции столбцов. Дополнительные сведения см. в разделе о сортировке хранилища данных.
  • REGEXP_LIKE возвращает булево значение и не может быть возвращено напрямую в виде столбца. Чтобы вернуть результат в виде столбца, оберните его в выражение CASE.

Examples

Следующие примеры используют небольшой встроенный набор данных, чтобы вы могли запускать их в любом хранилище или в конечной точке SQL-аналитики.

A. Фильтровать строки, соответствующие шаблону

Используйте REGEXP_LIKE в WHERE условии, чтобы возвращать только строки с действительным форматом адреса электронной почты.

SELECT CustomerId, Email
FROM (VALUES
        (1, 'anna@contoso.com'),
        (2, 'not-an-email'),
        (3, 'Bob.Smith@Fabrikam.org')
     ) AS c (CustomerId, Email)
WHERE REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Чтобы вернуть результат совпадения в виде столбца, оберните REGEXP_LIKE в выражение CASE:

SELECT Email,
       CASE WHEN REGEXP_LIKE(Email, '@contoso\.com$', 'i') THEN 'Internal' ELSE 'External' END AS EmailType
FROM (VALUES ('anna@CONTOSO.com'), ('bob@fabrikam.org')) AS c (Email);

B. Извлечь значение из текста

Используйте REGEXP_SUBSTR для извлечения первого номера заказа в формате ORD-, за которым следуют цифры, из комментария в свободном тексте.

SELECT Comment,
       REGEXP_SUBSTR(Comment, 'ORD-\d+') AS OrderNumber
FROM (VALUES
        ('Customer called about ORD-10293 and ORD-10311.'),
        ('No order referenced.')
     ) AS t (Comment);

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

SELECT REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'c', 1) AS EmailDomain
FROM (VALUES ('anna@contoso.com'), ('bob@fabrikam.org')) AS c (Email);

C. Подсчитать вхождения шаблона

Используй REGEXP_COUNT, чтобы подсчитать, сколько чисел появляется в каждой строке.

SELECT Comment,
       REGEXP_COUNT(Comment, '\d+') AS NumberCount
FROM (VALUES
        ('Shipped 3 boxes on 12 May, invoice 4471.'),
        ('No numbers here.')
     ) AS t (Comment);

D. Найдите позицию совпадения

Используйте REGEXP_INSTR для поиска начальной и конечной позиции первой последовательности цифр.

SELECT ProductCode,
       REGEXP_INSTR(ProductCode, '\d+')             AS FirstDigitStart,
       REGEXP_INSTR(ProductCode, '\d+', 1, 1, 1)    AS FirstDigitEnd
FROM (VALUES ('BK-R93R-62'), ('HL-U509')) AS p (ProductCode);

Е. Очистить и замаскировать данные

Используйте REGEXP_REPLACE для стандартизации номеров телефонов с помощью групп захвата и маскирования всех цифр, кроме последних четырёх.

SELECT Phone,
       REGEXP_REPLACE(Phone, '^(\d{3})-(\d{3})-(\d{4})$', '(\1) \2-\3') AS FormattedPhone,
       REGEXP_REPLACE(Phone, '^\d{3}-\d{3}', '***-***')               AS MaskedPhone
FROM (VALUES ('425-555-0187'), ('206-555-0142')) AS c (Phone);

Следующий запрос сжимает повторяющиеся пустые пространства в одно пространство:

SELECT REGEXP_REPLACE('Fabric    Data   Warehouse', '\s+', ' ') AS CleanText;

F. Группировка и агрегирование по извлеченному значению

Поскольку функции скалярные, их можно использовать в предложениях GROUP BY. Следующий запрос учитывает количество клиентов в каждом домене электронной почты.

SELECT LOWER(REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'c', 1)) AS EmailDomain,
       COUNT(*) AS Customers
FROM (VALUES
        ('anna@contoso.com'),
        ('bob@Contoso.com'),
        ('chris@fabrikam.org')
     ) AS c (Email)
GROUP BY LOWER(REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'c', 1));

Связанные материалы

  • Последнее обновление: