Używaj funkcji wyrażeń regularnych w Fabric Data Warehouse

Dotyczy:✅ końcowego punktu analizy SQL i magazynu danych w usłudze Microsoft Fabric

Funkcje wyrażeń regularnych (regex) pozwalają dopasowywać złożone wzorce tekstu i bezpośrednio manipulować danymi ciągów tekstów w zapytaniach T-SQL. Używaj ich do weryfikacji formatów, wyodrębniania wartości z półstrukturalnego tekstu, liczenia wystąpień wzorców oraz czyszczenia lub maskowania danych bez przenoszenia ich poza hurtownię danych.

Note

Wyrażenia regularne są obecnie w podglądzie.

Warehouse oraz endpoint analityki SQL obsługują skalarne funkcje wyrażeń regularnych. Każda z tych funkcji zwraca jedną wartość na wiersz wejściowy, więc możesz ich używać na liście SELECT oraz w wyrażeniach WHERE, JOIN, GROUP BY, ORDER BY i CASE.

Note

W Warehouse dostępne są tylko skalarne funkcje wyrażeń regularnych. Funkcje tabelowe REGEXP_MATCHES i REGEXP_SPLIT_TO_TABLE nie są obsługiwane.

Obsługiwane funkcje

Funkcja Typ zwracany Description
REGEXP_LIKE logiczny Wskazuje, czy wejście tekstowe odpowiada wzorcowi regex. Użyj go jako predykatu, na przykład w zdaniu WHERE lub wyrażeniu CASE .
REGEXP_REPLACE Wyrażenie ciągowe Zwraca łańcuch źródłowy z wystąpieniami wzorca regex zastąpionymi ciągiem zastępczym. Jeśli nie znaleziono żadnych dopasowań, zwraca oryginalny ciąg znaków.
REGEXP_SUBSTR Wyrażenie łańcuchowe Zwraca n-tewystąpienie podciągu odpowiadającego wzorcowi regex.
REGEXP_INSTR int Zwraca pozycję początkową lub końcową dopasowanego podciągu.
REGEXP_COUNT int Zwraca liczbę razy, gdy wzór regex pojawia się w ciągu znaków.

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 ] ] )

Pełny opis każdego argumentu można znaleźć w podlinkowanej dokumentacji funkcji w sekcji Wspierane funkcje.

Flagi

Opcjonalnym argumentem flags jest wartość varchar lub char o długości do 30 znaków, która ustala pasujące modyfikatory. Jeśli podasz sprzeczne flagi, wygrywa ta ostatnia. Na przykład 'ic' powoduje dopasowywanie z rozróżnianiem wielkości liter.

Flaga Description
c Dopasowanie uwzględniające wielkość liter. Jest to opcja domyślna.
i Dopasowywanie bez uwzględniania wielkości liter.
m Tryb wielolinijkowy: ^ i $ odpowiadają początkowi i końcowi każdej linijki, a także początkowi i końcowi tekstu.
s Dopasujmy . znak \n nowej linii.

Considerations

  • string_expression Argument akceptuje typy łańcuchów znaków. Obsługiwane są duże wartości (varchar(max)) do 2 MB.
  • Ten argument pattern_expression obsługuje maksymalną długość 8 000 bajtów.
  • Funkcje wyrażeń regularnych nie przestrzegają reguł sortowania. Dopasowywanie może różnić się od innych porównań ciągów znaków, takich jak LIKE. Aby kontrolować czułość wielkości liter, użyj flag i i c zamiast polegać na kolacji kolumny. Aby uzyskać więcej informacji, zobacz zestawienie Data Warehouse.
  • REGEXP_LIKE zwraca wartość boolowską i nie może być zwracana bezpośrednio jako kolumna. Aby zwrócić wynik jako kolumnę, opakuj go w wyrażenie CASE.

Examples

Poniższe przykłady używają małego wbudowanego zbioru danych, więc możesz je uruchomić w dowolnej hurtowni danych lub punkcie końcowym analityki SQL.

A. Filtruj wiersze pasujące do wzorca

Użyj REGEXP_LIKE w klauzuli WHERE, aby zwracać tylko wiersze o prawidłowym formacie adresu e-mail.

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,}$');

Aby zwrócić wynik dopasowania jako kolumnę, opakuj REGEXP_LIKE w wyrażenie 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. Wyodrębnij wartość z tekstu

Użyj REGEXP_SUBSTR, aby wyodrębnić pierwszy numer zamówienia w formacie ORD-, po którym następują cyfry, z komentarza w wolnym tekście.

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);

Aby wydobyć tylko część dopasowania, użyj grupy przechwytującej. Następujące zapytanie zwraca domenę każdego adresu e-mail:

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

C. Zlicz wystąpienia wzorców

Użyj REGEXP_COUNT, aby policzyć, ile liczb pojawia się w każdym ciągu.

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. Znajdź pozycję wystąpienia dopasowania

Użyj REGEXP_INSTR do znalezienia pozycji początkowej oraz pozycji końcowej pierwszej sekwencji cyfr.

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);

E. Czyść i maskuj dane

Używa się REGEXP_REPLACE do standaryzacji numerów telefonów z grupami przechwytywania oraz do maskowania wszystkich cyfr poza ostatnimi czterema.

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);

Następujące zapytanie redukuje powtarzające się białe znaki do jednej spacji:

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

F. Grupuj i agreguj według wyodrębnionej wartości

Ponieważ funkcje są skalarne, można ich używać w GROUP BY wyrażeniach. Poniższe zapytanie liczy klientów według domeny e-mail.

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));

Treści powiązane

  • Ostatnia aktualizacja o