MS SQL funkcja CHARINDEX - opis funkcji i przykłady wykorzystania
Szybki kontakt




W artykule znajdziesz szczegółowy opis funkcji CHARINDEX() wraz z praktycznymi przykładami jej wykorzystania. Przykłady możesz uruchomić w bazie Northwind lub AventureWorks.

Składnia funkcji CHARINDEX()

Funkcja CHARINDEX() wyszukuje jedno wyrażenie tekstowe w innym wyrażeniu tekstowym. Jeśli szukane wyrażenie zostanie znalezione, zwracana jest pozycja początkowa znalezionego wyrażenia.

CHARINDEX() argumenty funkcji

Argumenty funkcji:
search_expression varchar(1) – tekst, którego szukamy. Tekst może zawierać jeden lub więcej znaków. Słowo „varchar” to informacja, że w argumencie musi być podany ciąg znaków.

expression_to_be_searched varchar(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.

start_location int – argument opcjonalny, w którym możemy podać, od którego znaku w argumencie „expression_to_be_searched” należy zacząć poszukiwanie. Słowo „int” to informacja, że w argumencie musi zostać podana liczba całkowita (integer).

Jeśli w argumencie „start_location” nie podamy żadnej wartości, podamy wartość ujemną lub wartość zero, wyszukiwanie rozpocznie się od początku wyrażenia podanego w argumencie „expression_to_be_searched”.

Funkcja zwraca w wyniku int lub bigint, jeśli w argumencie „expression_to_be_searched” wystąpi typ danych nvarchar(max), varbinary(max) lub varchar(max).

Zwraca ona pozycję pierwszego wystąpienia szukanego znaku lub wyrażenia licząc od jedynki. Pierwsza pozycja znalezionego znaku lub wyrażenia oznaczona jest jedynką a nie zerem.

Przykłady wykorzystania funkcji – znajdujemy szukane wyrażenie

Przykład 1

SELECT CHARINDEX('m', 'Myszka Miki');

Wynik:
———————-
1

W przykładzie szukamy litery „m” w ciągu znaków „Myszka Miki”. Litera m jest na pierwszej pozycji w przeszukiwanym ciągu znaków. Dlatego w wyniku zwrócona została wartość 1.

W argumencie „start_location” nie wpisujemy żadnej wartości, więc przeszukiwanie zaczyna się od początku wyrażenia podanego w argumencie „expression_to_be_searched” tj. od początku tekstu „Myszka Miki”.

Przykład 2

SELECT CHARINDEX('a', 'Myszka Miki');

Wynik:
———————-
6

W przykładzie szukamy litery „a” w ciągu znaków „Myszka Miki”. Litera a jest na szóstej pozycji w przeszukiwanym ciągu znaków. Dlatego w wyniku zwrócona została wartość 6.

W argumencie „start_location” nie wpisujemy żadnej wartości, więc przeszukiwanie zaczyna się od początku wyrażenia podanego w argumencie „expression_to_be_searched” tj. od początku tekstu „Myszka Miki”..

Przykład 3

SELECT CHARINDEX('Mysz', 'Myszka Miki');

Wynik:
———————-
1

W przykładzie szukamy ciągu tekstowego „Mysz” w ciągu tekstowym „Myszka Miki”. Znaleziony ciąg znaków „Mysz” zaczyna się na pozycji pierwszej w przeszukiwanym tekście. Dlatego w wyniku zwrócona została wartość 1.

W argumencie „start_location” nie wpisujemy żadnej wartości, więc przeszukiwanie zaczyna się od początku wyrażenia podanego w argumencie „expression_to_be_searched” tj. od początku tekstu „Myszka Miki”.

Przykład 4

SELECT CHARINDEX('to', 'Myszka to Miki, Kaczor to Donald',10);

Wynik:
———————-
24

W przykładzie szukamy ciągu tekstowego „to” w ciągu tekstowym „Myszka to Miki, Kaczor to Donald”.

W argumencie „start_location” podaliśmy wartość 10, więc przeszukiwanie zaczyna się od 10 znaku wyrażenia podanego w argumencie „expression_to_be_searched” „Myszka to Miki, Kaczor to Donald”.

Dlatego znalezione zostało drugie wystąpienie słowa „to”. Pierwszy znak drugiego wystąpienia wyrażenia „to” znajduje się na 24 pozycji (licząc od początku ciągu tekstowego), więc w wyniku otrzymaliśmy 24.

Przykład wykorzystania funkcji – nie znajdujemy szukanego wyrażenia

Jeśli funkcja CHARINDEX nie znajdzie szukanego wyrażenia zwraca wartość zero.

Przykład 5

SELECT CHARINDEX('w', 'Myszka Miki');

Wynik:
———————-
0

W przykładzie szukamy litery „w” w ciągu tekstowym „Myszka Miki”.

Litera „w” nie została znaleziona, więc w wyniku otrzymujemy zero.

Przykład wykorzystania funkcji – wartość specjalna NULL w wyrażeniu

Jeśli któreś z wyrażeń (szukane lub przeszukiwane) ma wartość specjalną NULL, funkcja CHARINDEX() zwraca NULL.

Przykład 6

SELECT CHARINDEX('a', NULL);

Wynik:
———————-
NULL

W przykładzie szukamy litery „a” w wyrażeniu o wartości specjalnej NULL. W wyniku otrzymujemy NULL.

Praktyczne przykłady z baz danych Northwind i AdventureWorks2017

Przykład 7

USE AdventureWorks2017;
SELECT 
   EmailAddress, 
   CHARINDEX('@', EmailAddress) Pozycja_Znaku_T1, --wyświetlane testowo
   LEFT(EmailAddress,  CHARINDEX('@', EmailAddress)) as Nazwa_Uzytkownika_T1,
   CHARINDEX('@', EmailAddress) -1 Pozycja_Znaku_T2 ,--wyświetlane testowo
   LEFT(EmailAddress,  CHARINDEX('@', EmailAddress)-1) as Nazwa_Uzytkownika_T2
FROM Person.EmailAddress;

Wynik:

Funkcja CHARNIDEX() przykład 7

W przykładzie chcemy z adresu e-mail (kolumna EmailAddress) wyciągnąć nazwę użytkownika:

  • za pomocą funkcji CHARINDEX() znajdujemy, na której pozycji znajduje się znak małpy (@). Wynik działania funkcji znajduje się w kolumnie o nazwie Pozycja_Znaku_T1
  • za pomocą funkcji LEFT() z kolumny EmailAddress wyciągamy wszystkie znaki od lewej strony do pozycji, która wyliczona została za pomocą funkcji CHARINDEX(). Ilość wyciąganych znaków określana jest przez wynik funkcji CHARINDEX(). Przykładowo w pierwszym rekordzie, funkcja CHARINDEX() zwróciła informację, że znak małpy jest na 3 pozycji, więc funkcja LEWY() zwróciła 3 znaki. W wyniku (w kolumnie Nazwa_Uzytkownika_T1) mamy zatem nazwę użytkownika razem ze znakiem małpy.
  • w kolumnie Nazwa_Uzytkownika_T2 wyświetlamy nazwę użytkownika bez znaku małpy. Aby to zrobić musieliśmy od wyniku zwróconego przez CHARINDEX() odjąć jeden znak. W kolumnie o nazwie Pozycja_Znaku_T2 wyświetlamy testowo wynik działania funkcji CHARDINEX() minus 1 znak.

Przykład 8

USE northwind;
SELECT
ContactName,
CHARINDEX(' ', [ContactName]) Spacja,
SUBSTRING([ContactName], 1, CHARINDEX(' ', [ContactName]) - 1) Imię,
SUBSTRING([ContactName], CHARINDEX(' ', [ContactName]) + 1, 9999) Nazwisko
from Customers;

Wynik:

Funkcja CHARNIDEX() przykład 7

W przykładzie chcemy wyświetlić w oddzielnych kolumnach imię i nazwisko znajdujące się w kolumnie ContactName:

  • za pomocą funkcji CHARINDEX() znajdujemy, na której pozycji znajduje się znak spacji. Wynik działania funkcji znajduje się w kolumnie o nazwie Spacja
  • za pomocą funkcji SUBSTRING() z kolumny ContactName wyciągamy imię. Zaczynamy od pierwszego znaku (licząc od lewej). Informacji ile znaków mamy wyciągnąć dostarcza nam funkcja CHARINDEX(). Odejmujemy jedynkę, aby nie wyciągać spacji. Wynik mamy w kolumnie o nazwie Imię
  • za pomocą funkcji SUBSTRING() z kolumny ContactName wyciągamy nazwisko. Informacji, od którego znaku mamy zacząć „wycinanie” dostarcza nam funkcja CHARINDEX(). CHARINDEX() zwraca informację, na której pozycji jest spacja. Dodajemy jedynkę, aby zacząć wycinanie od pierwszego znaku po spacji.
  • w trzecim argumencie funkcji SUBSTRING() podajemy ile znaków chcemy „wyciąć”. Wpisaliśmy tutaj 9999, co pozwoli nam na wycięcie wszystkich znaków od pierwszego znaku po spacji do końca tj. do prawej strony ciągu tekstowego

Dodatkowe informacje

Jeśli wyrażenie podane w argumencie „expression_to_be_searched” ma typ danych Unicode (nchar lub nvarchar), a wyrażenie podane w argumencie „search_expression” ma inny typ danych, funkcja CHARINDEX przekonwertuje wyrażenie z „search_expression” na typ danych Unicode i odwrotnie.

Funkcja CHARINDEX nie może być używana z typami danych image, ntext lub text.

Sposób, w jaki funkcja CHARINDEX() traktuje małe i wielkie litery zależy od ustawionego w SQL Server collation. Jeśli collation w SQL Server ustawione zostało w taki sposób, że mają nie być rozróżniane małe i wielkie litery, funkcja CHARINDEX() też ich nie rozróżnia.

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

Dodaj komentarz

Twój adres email nie zostanie opublikowany.