MS SQL - funkcja PATINDEX() - opis funkcji wraz z przykładami
Szybki kontakt




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.

PATINDEX() argumenty funkcji

Argumenty funkcji:
search_pattern_literal nvarchar(1) – wyrażenie zawierające sekwencję znaków do znalezienia. Możemy używać znaków 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. We pierwszym argumencie podajemy wzorzec, zgodnie z którym szukamy w tekście z drugiego argumentu liczby znajdującej się między zerem a 9. 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

SELECT PATINDEX('%[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, w tekście z drugiego argumentu, 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.

Przykład 3

SELECT PATINDEX('%[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().

  SELECT  
  SUBSTRING('ul. Niebieska 2, 01-123 Warszawa',--pierwszy argument funkcji SUBSTRING()
  PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%','ul. Niebieska 2, 01-123 Warszawa'),
  --funkcja PATINDEX() jako drugi argument funkcji SUBSTRING()
   6) --szóstka jako 3 argument funkcji SUBSTRING()

W argumentach funkcji SUBSTRING() podajemy:

  • argument 1 – adres jako ciąg tekstowy
  • argument 2 – od którego znaku chcemy wyświetlić fragment ciągu tekstowego. Użyliśmy tutaj funkcji PATINDEX(), która zwraca nam informację, na której pozycji znajduje się początek ciągu znaków dopasowanego do wzorca
  • argument 3 – podajemy ilość znaków, które chcemy wyświetlić. 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:

Funkcja PATINDEX() przykład 7

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.

Jak zawsze zachęcam do pozostawiania pytań i komentarzy.

Dodaj komentarz

Twój adres email nie zostanie opublikowany.