Data: 16.07.2021
MS SQL funkcja PATINDEX
Spis treści
W artykule przedstawiam funkcję PATINDEX wraz z praktycznymi przykładami jej wykorzystania.
Przykłady możesz uruchomić w bazie Northwind lub AventureWorks.
Składnia funkcji PATINDEX()
Funkcja PATINDEX() zwraca pozycję początkową pierwszego wystąpienia wzorca w określonym wyrażeniu lub zero, jeśli wzorzec nie zostanie znaleziony, dla wszystkich poprawnych typów danych tekstowych i znakowych.
Argumenty funkcji:
search_pattern_literal nvarchar(1) – wyrażenie zawierające sekwencję znaków do znalezienia. Możemy używać znaku wieloznacznego tj. znaku procent (%). Jeśli nie wyszukujemy pierwszych lub ostatnich znaków, znak procent (%) musi znajdować się zarówno przed jak i za wzorcem np. '%[0-9]%’. Wzorzec jest ograniczony do 8000 znaków.
W argumencie tym możemy używać znaków wieloznacznych:
- procent (%) – dopasowanie dowolnego ciągu znaków o dowolnej długości np. 'Myszka Mi%’
- podkreślenie (_) – dopasowanie jednego znaku np. 'Myszka Mi_ki’
- para kwadratowych nawiasów ([]) – dopasowanie dowolnego znaku, znajdującego się w nawiasach np. [acp]
- znak karety w parze kwadratowych nawiasów ([^]) – dopasowanie dowolnego znaku, z wyjątkiem tych znaków, które są w nawiasie kwadratowym poprzedzone znakiem karety (^) np. [^acp]
expression nvarchar(1) – tekst, który przeszukujemy, aby znaleźć wyrażenie podane w argumencie „search_expression”. Słowo „varchar” to informacja, że w argumencie musi być podany ciąg znaków.
Funkcja zwraca w wyniku int lub bigint, jeśli w argumencie „search_pattern_literal” wystąpi typ danych nvarchar(max), varbinary(max) lub varchar(max).
Zwraca ona pozycję pierwszego wystąpienia szukanego wzorca licząc od jedynki. Pierwsza pozycja znalezionego wzorca oznaczona jest jedynką a nie zerem.
Przykłady wykorzystania funkcji – znajdujemy szukany wzorzec
Przykład 1
SELECT PATINDEX('%[0-9]%','ul. Niebieska 2, 01-123 Warszawa');Wynik: 15
W przykładzie chcemy znaleźć, na której pozycji znajduje się numer domu w adresie.
W pierwszym argumencie podajemy wzorzec, zgodnie z którym szukamy w tekście liczby znajdującej się między zerem a dziewiątką. Znaleziona została liczba 2 znajdująca się na 15 pozycji ciągu znaków 'ul. Niebieska 2, 01-123 Warszawa’. Dlatego w wyniku otrzymujemy 15.
Przykład 2
SELECTPATINDEX('%[0-9][0-9]%','ul. Niebieska 2, 01-123 Warszawa');Wynik: 18
W przykładzie chcemy znaleźć, na której pozycji znajduje się pierwsza część kodu pocztowego.
W pierwszym argumencie podajemy wzorzec, zgodnie z którym szukamy dwóch następujących po sobie liczb. Zarówno pierwsza jak i druga szukana liczba muszą być z zakresu od zera do 9. Znaleziony został ciąg znaków „01”, którego początek zaczyna się na pozycji 18. Dlatego w wyniku otrzymujemy 18. Przeszukiwany tekst to „ul. Niebieska 2, 01-123 Warszawa”.
Przykład 3
SELECTPATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%','ul. Niebieska 2, 01-123 Warszawa');Wynik: 18
W przykładzie chcemy znaleźć pełen kod pocztowy. W argumencie pierwszym podajemy wzorzec kodu pocztowego tj. we wzorcu ma być dwie cyfry (każda z nich w zakresie od 0 do 9), następnie myślnik, a po myślniku trzy cyfry, z których każda również musi być w zakresie od 0 do 9.
Znaleziony został ciąg znaków „01-123”, którego początek zaczyna się na pozycji 18. Dlatego w wyniku otrzymujemy 18.
Aby wyświetlić cały kod pocztowy potrzebujemy funkcji SUBSTRING().
Za pomocą funkcji SUBSTRING() możemy z tekstu wyodrębnić jeden lub więcej znaków. W przykładzie wyodrębniamy kod pocztowy. Musimy w argumentach funkcji podać przeszukiwany tekst oraz informację, od którego znaku zaczynamy wyodrębnianie i ile znaków chcemy wyodrębnić.
SELECT SUBSTRING('ul. Niebieska 2, 01-123 Warszawa', PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%', 'ul. Niebieska 2, 01-123 Warszawa'),6)W argumentach funkcji SUBSTRING() podajemy:
- argument 1 – adres jako ciąg tekstowy, z którego będziemy wyodrębniać kod pocztowy
- argument 2 – informacja, od którego znaku chcemy rozpocząć wyodrębnianie tekstu. Użyliśmy tutaj funkcji PATINDEX(), która zwraca nam informację, na której pozycji znajduje się początek tekstu dopasowanego do wzorca tj. cyfra zero, która jest początkiem kodu pocztowego. Od niej zaczynamy wyodrębnianie.
- argument 3 – podajemy ilość znaków, które chcemy wyodrębnić. W naszym przykładzie jest to 6 znaków.
Wynik: 01-123
Przykład wykorzystania funkcji – nie znajdujemy szukanego wyrażenia
Jeśli funkcja PATINDEX() nie znajdzie wyrażenia pasującego do wzorca, zwróci wartość 0.
Przykład 5
SELECT PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%','ul. Niebieska 2, AB-CDE Warszawa');Wynik: 0
W przykładzie szukamy wyrażenia, zgodnego ze wzorcem, w którym dwa pierwsze znaki to cyfry (każda z nich w zakresie od 0 do 9), następnie myślnik, a po myślniku trzy cyfry, z których każda również musi być w zakresie od 0 do 9.
W tekście, który przeszukujemy nie zostało znalezione wyrażenie pasujące do tego wzorca, dlatego otrzymujemy wartość zero w wyniku.
Przykład wykorzystania funkcji – wartość specjalna NULL we wzorcu lub w wyrażeniu
Jeśli pierwszy argument (wzorzec) funkcji PATINDEX() ma wartość specjalną NULL, funkcja zwraca w wyniku NULL.
Przykład 6
SELECT PATINDEX(NULL,'ul. Niebieska 2, 01-123 Warszawa');Wynik: NULL
Jeśli drugi argument funkcji (przeszukiwany ciąg znaków) ma wartość specjalną NULL, funkcja zwraca błąd.
SELECT PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%',NULL);Komunikat błędu:
Msg 8116, Level 16, State 1, Line 3 Argument data type NULL is invalid for argument 2 of patindex function
Przykład z bazy danych Northwind
Przykład 7
USE northwind;SELECT Title, PATINDEX('%Sales%',Title)FROM Employees;Wynik:
W przykładzie podajemy we wzorcu słowo „Sales” i chcemy dowiedzieć się, na której pozycji w ciągu tekstowym słowo to występuje. W wyniku otrzymujemy pozycję pierwszego znaku, od którego słowo „Sales” zaczyna się w tekście.
Programista SQL, Cloud Developer
