Data: 8.07.2021
MS SQL funkcja CHARINDEX
Spis treści
- MS SQL funkcja CHARINDEX
- MS SQL funkcja CHARINDEX – składnia funkcji
- Argumenty funkcji CHARINDEX
- Przykłady funkcji CHARINDEX() – znajdujemy szukane wyrażenie
- Przykłady funkcji CHARINDEX() – nie znajdujemy szukanego wyrażenia
- CHARINDEX i wartość specjalna NULL w wyrażeniu
- Przykłady z baz danych Northwind i AdventureWorks2017
- CHARINDEX, a małe i wielkie litery
W artykule MS SQL funkcja CHARINDEX znajdziesz szczegółowy opis funkcji CHARINDEX wraz z praktycznymi przykładami jej wykorzystania.
Przykłady możesz uruchomić w bazie Northwind lub AventureWorks.
MS SQL funkcja CHARINDEX – składnia funkcji
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.
Argumenty funkcji CHARINDEX
Argumenty funkcji CHARINDEX:
- 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 należy zacząć poszukiwanie. Słowo „int” to informacja, że w argumencie musi zostać podana liczba całkowita (integer).
Wyszukiwanie rozpocznie się od początku przeszukiwanego tekstu, jeśli w argumencie „start_location”:
- nie podamy żadnej wartości
- podamy wartość ujemną
- podamy wartość zero
Funkcja zwraca pozycję pierwszego wystąpienia szukanego znaku lub tekstu licząc od jedynki.
Przykłady funkcji CHARINDEX() – znajdujemy szukane wyrażenie
Poniżej znajdziesz przykłady zastosowania funkcji CHARINDEX(), w których znajdujemy szukany znak lub wyrażenie.
Przykład 1
SELECT CHARINDEX('s', 'Język SQL');
-- Wynik: 7W przykładzie szukamy litery „s” w ciągu znaków „Język SQL”. Litera „s” jest na siódmej pozycji w przeszukiwanym ciągu znaków. Dlatego w wyniku zwrócona została wartość 7.
W argumencie „start_location” nie wpisujemy żadnej wartości, więc przeszukiwanie zaczyna się od początku tekstu „Język SQL”.
Przykład 2
SELECT CHARINDEX('SQL', 'Język SQL');
-- Wynik: 7W przykładzie szukamy ciągu tekstowego „SQL” w ciągu tekstowym „Język SQL”. Znaleziony ciąg znaków „SQL” zaczyna się na pozycji siódmej w przeszukiwanym tekście. Dlatego w wyniku zwrócona została wartość 7.
W argumencie „start_location” nie wpisujemy żadnej wartości, więc przeszukiwanie zaczyna się od początku tekstu „Język SQL”.
Przykład 3
SELECT CHARINDEX('język', 'język SQL, język Python',10);
-- Wynik: 12W przykładzie szukamy ciągu tekstowego „język” w ciągu tekstowym „język SQL, język Python”.
W argumencie „start_location” podaliśmy wartość 10, więc przeszukiwanie zaczyna się od 10 znaku wyrażenia „język SQL, język Python”.
W związku z tym, że rozpoczynamy przeszukiwanie od 10 znaku znalezione zostało drugie wystąpienie słowa „język”. Pierwszy znak drugiego wystąpienia słowa „język” znajduje się na 12 pozycji (licząc od początku ciągu tekstowego), więc w wyniku otrzymaliśmy 12.
Przykłady funkcji CHARINDEX() – nie znajdujemy szukanego wyrażenia
Jeśli funkcja CHARINDEX nie znajdzie szukanego wyrażenia zwraca wartość zero.
Przykład 4
SELECT CHARINDEX('Python', 'Język SQL');
-- Wynik: 0W przykładzie szukamy tekstu „Python” w tekście „Język SQL”.
Tekst „Python” nie został znaleziony, więc w wyniku otrzymujemy zero.
CHARINDEX i 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 5
SELECT CHARINDEX('a', NULL);
-- Wynik: NULLW przykładzie szukamy litery „a” w wyrażeniu o wartości specjalnej NULL. W wyniku otrzymujemy NULL.
Przykłady z baz danych Northwind i AdventureWorks2017
Przykład 6
USE AdventureWorks2017;
SELECTEmailAddress,CHARINDEX('@', EmailAddress) as t1,LEFT(EmailAddress, CHARINDEX('@', EmailAddress)-1) as t2FROM Person.EmailAddress;Wynik:
W przykładzie chcemy z adresu e-mail (kolumna EmailAddress) odczytać nazwę użytkownika np. nazwę „a0”.
Za pomocą funkcji CHARINDEX() znajdujemy, na której pozycji znajduje się znak małpy (@). Wynik działania funkcji znajduje się w kolumnie o nazwie t1.
Za pomocą funkcji LEFT(), z kolumny EmailAddress odczytujemy znaki od lewej strony.
Odczytujemy tyle znaków, ile jest w wyniku, który zwróciła funkcja CHARINDEX(). W związku z tym w wyniku działania funkcji LEFT() otrzymamy nazwę użytkownika razem ze znakiem małpy.
Aby nie odczytywać znaku małpy, od wyniku działania funkcji CHARINDEX() odejmujemy jeden znak. Wynik znajduje się w kolumnie t2
Przykład 7
USE northwind;SELECTContactName,CHARINDEX(' ', [ContactName]) Spacja,SUBSTRING([ContactName], 1, CHARINDEX(' ', [ContactName]) - 1) Imię,SUBSTRING([ContactName], CHARINDEX(' ', [ContactName]) + 1, 9999) Nazwiskofrom Customers;Wynik:
| ContactName | Spacja | Imię | Nazwisko | |
|---|---|---|---|---|
| 1 | Maria Anders | 6 | Maria | Anders |
| 2 | Ana Trujillo | 4 | Ana | Trujillo |
| 3 | Antonio Moreno | 8 | Antonio | Moreno |
| 4 | Thomas Hardy | 7 | Thomas | Hardy |
| 5 | Christina Berglund | 10 | Christina | Berglund |
| 6 | Hanna Moos | 6 | Hanna | Moos |
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 odczytujemy imię. Zaczynamy od pierwszego znaku (licząc od lewej). Informacji ile znaków mamy odczytać dostarcza nam funkcja CHARINDEX(). Odejmujemy jedynkę od wyniku funkcji CHARINDEX(), aby nie odczytywać spacji. Wynik mamy w kolumnie o nazwie Imię
- za pomocą funkcji SUBSTRING() z kolumny ContactName odczytujemy nazwisko. Informacji, od którego znaku mamy zacząć odczytywanie dostarcza nam funkcja CHARINDEX(). CHARINDEX() zwraca informację, na której pozycji jest spacja. Dodajemy jedynkę, aby zacząć odczytywanie od pierwszego znaku po spacji.
- w trzecim argumencie funkcji SUBSTRING() podajemy ile znaków chcemy odczytać. Wpisaliśmy tutaj 9999, co pozwoli nam na odczytanie wszystkich znaków od pierwszego znaku po spacji do końca tj. do prawej strony ciągu tekstowego
Opis funkcji LEFT() i SUBSTRING() znajdziesz w artykule Funkcje tekstowe w SQL Server.
CHARINDEX, a małe i wielkie litery
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.
Przedstawione wyżej przykłady oraz ponad 100 innych ćwiczeń znajdziesz na naszym kursie SQL na poziomie średnio-zaawansowanym.
Programista SQL, Cloud Developer
