:~$ newsletter --subscribe
cat ~/artykuly/bazy-danych/przeszukiwanie-danych-za-pomoca-operatora-like-oraz-ich-sortowanie-order-by-w-sql.md

Przeszukiwanie danych za pomocą operatora LIKE oraz ich sortowanie w SQL

SQL sam w sobie za bardzo nie nadaje się do przeszukiwania tekstów (przy pomocy wbudowanych funkcji) ale na drobne potrzeby możemy wykorzystać operator LIKE w klauzuli WHERE, który wyszukuje tekst z danej kolumny pasujący do podanego wzorca. Po otrzymaniu już wyników wszystko oczywiście możemy odpowiednio posortować z poziomu zapytania. Sortowanie wykonujemy za pomocą operatora ORDER BY. Obie funkcje zostały dokładnie opisane poniżej…

Operator LIKE

Język SQL, umożliwia bardzo prostą metodę pobierania różnych danych na podstawie warunków logicznych. Co jednak zrobić gdy musimy pobrać rekord zawierający jakiś ciąg znaków? A co jeśli dana sekwencja musi występować w środku jakiegoś słowa? Na szczęście rozwiązanie tych problemów jest również bardzo proste, musimy zastosować tzw. klauzule „LIKE” języka SQL.

Pobierzmy z bazy rekord, który w kolumnie „Miasto”, zawiera ciąg znaków „kra”:

sql
SELECT * FROM tabela WHERE miasto LIKE '%kra%';

Powyższe zapytanie pobiera z bazy wszystkie rekordy, w których wpisana treść w kolumnie „miasto” zawiera ciąg znaków „kra”. Ciąg ten może być na początku jakiegoś słowa lub na jego końcu oraz w środku czyli przed i za literami „kra” mogą znajdować się dowolne znaki. Umożliwia nam to znak „%”, który w języku SQL zastępuje dowolną literę lub cyfrę.

Pobierzmy z bazy informacje o pracownikach, których imię zaczyna się na literę „Ł”:

sql
SELECT * FROM tabela WHERE imie LIKE 'Ł%';

Rozwiązanie tego problem było bardzo podobne do poprzedniego. Tutaj wystarczyło tylko dopisać po literze „Ł” znak %.

Pobierzmy z bazy informacje o pracownikach, których imię zaczyna się na literę z przedziału od „A” do „N”:

sql
SELECT * FROM tabela WHERE imie REGEXP '^[A-N]';

Przedział liter ujęliśmy w nawiasy kwadratowe. Taką klasę znaków obsługuje w MySQL operator REGEXP (wyrażenia regularne), bo sam LIKE zna wyłącznie wieloznaczniki „%” i „_”.

Pobierzmy teraz informacje o pracownikach, których nazwisko zaczyna się na literę „D”, a trzecią literą jest litera „K”:

sql
SELECT * FROM tabela WHERE nazwisko LIKE 'D_k%';

W zapytaniu tym został użyty znak „_”, który w MySQL zastępuje dokładnie jeden dowolny znak (znak „?” nie jest wieloznacznikiem operatora LIKE).

Pobierzmy teraz dane pracowników, których imię nie zaczyna się na literę „C”:

sql
SELECT * FROM tabela WHERE imie NOT LIKE 'C%';

Operator NOT LIKE neguje wzorzec: powyższy warunek zwraca rekordy, w których wartość kolumny imie nie zaczyna się na literę „C” (znak „!” nie jest w MySQL wieloznacznikiem).

Pamiętaj: porównania LIKE w MySQL domyślnie nie rozróżniają dużych i małych liter. Decyduje o tym kolacja (collation) ustawiona dla kolumny.

Sortowanie (ORDER BY)

Pobrane za pomocą zapytania SELECT dane można oczywiście posortować według zawartości dowolnej kolumny. Warto tutaj zwrócić uwagę iż mamy dwie możliwości sortowania:

SortowanieOpis
ASCSortowanie od elementu najmniejszego do największego (domyślnie)
DESCSortowanie od elementu największego do najmniejszego

Weźmy przykładową tabelę (klienci):

IdImieNazwisko
1JanKowalski
3MaciejMaciejewski
2TomaszNowak
4MartaNowak
5MichałGadulski

Jak widać kolumna Id nie jest w ogóle posortowana. Posortujmy więc ją w sposób domyślny (ASC):

sql
SELECT * FROM klienci ORDER BY Id;

to zapytanie jest równoważne temu:

sql
SELECT * FROM klienci ORDER BY Id ASC;

Efekt:

IdImieNazwisko
1JanKowalski
2TomaszNowak
3MaciejMaciejewski
4MartaNowak
5MichałGadulski

Sortowanie od największego do najmniejszego:

sql
SELECT * FROM klienci ORDER BY Id DESC;

Efekt:

IdImieNazwisko
5MichałGadulski
4MartaNowak
3MaciejMaciejewski
2TomaszNowak
1JanKowalski

Należy pamiętać, że zgodnie ze schematem podanym w artykule „Podstawowa wersja zapytania SELECT w SQL (SELECT … FROM … WHERE …)” klauzulę ORDER BY umieszczamy na końcu zapytania SQL.

Symbole wieloznaczne: % oraz _

Cała siła operatora LIKE tkwi w dwóch symbolach wieloznacznych (ang. wildcards), które zastępują nieznane fragmenty tekstu:

  • % – zastępuje dowolny ciąg znaków (również pusty),
  • _ – zastępuje dokładnie jeden dowolny znak.

Kilka przykładów najlepiej pokazuje różnicę między nimi:

sql
SELECT * FROM klienci WHERE imie LIKE 'A%';    -- zaczyna sie na "A"
SELECT * FROM klienci WHERE imie LIKE '%a';    -- konczy sie na "a"
SELECT * FROM klienci WHERE imie LIKE '%an%';  -- zawiera "an" w srodku
SELECT * FROM klienci WHERE imie LIKE '_an';   -- 3 litery, konczy sie na "an" (np. "Jan")
SELECT * FROM klienci WHERE imie LIKE 'A____'; -- "A" i dokladnie 4 kolejne znaki

Znak ucieczki (ESCAPE)

Co jeśli sami szukamy tekstu zawierającego znak procenta albo podkreślenia, na przykład rabatu „50%”? Musimy wtedy powiedzieć bazie, że dany symbol traktujemy dosłownie, a nie jako wieloznacznik. Służy do tego klauzula ESCAPE, która definiuje znak ucieczki:

sql
SELECT * FROM promocje WHERE opis LIKE '%50\%%' ESCAPE '\';

Zapis \% oznacza teraz dosłowny znak procenta, a stojący za nim % to nadal zwykły wieloznacznik. Dzięki temu zapytanie znajdzie każdy opis zawierający ciąg „50%”.

NOT LIKE oraz wielkość liter

Warunek można odwrócić operatorem NOT LIKE, który zwraca wiersze niepasujące do wzorca:

sql
SELECT * FROM klienci WHERE email NOT LIKE '%@gmail.com';

Pamiętajmy, że rozróżnianie wielkości liter przez LIKE zależy od bazy danych i ustawień sortowania (ang. collation). W MySQL domyślnie LIKE nie rozróżnia wielkości liter, a w PostgreSQL rozróżnia (i ma do tego drugiego osobny operator ILIKE). Jeśli chcemy mieć pewność, zawsze możemy sprowadzić obie strony do jednej wielkości funkcją LOWER().

LIKE a wydajność

Została jeszcze ważna kwestia praktyczna. Wzorzec zaczynający się od stałego tekstu (np. 'Kowalski%') baza może przyspieszyć indeksem, tak jak szukanie hasła w słowniku, gdzie znamy pierwsze litery. Natomiast wzorzec zaczynający się od % (np. '%kowalski') zmusza bazę do przejrzenia wszystkich wierszy, bo nie wie, od czego zacząć. Na małej tabeli nie ma to znaczenia, ale przy setkach tysięcy rekordów robi ogromną różnicę. Gdy potrzebujemy szybkiego wyszukiwania fragmentów w środku tekstu, zamiast LIKE '%...%' lepiej sięgnąć po indeksy pełnotekstowe (ang. full-text search), które bazy danych oferują właśnie do tego celu.

Łukasz Dudziński
Łukasz Dudziński · założyciel i autor
Pasjonat informatyki, a zwłaszcza programowania. Zwariowany maniak na tle nowych technologii oraz komputerów. W wolnych chwilach bloger... więcej-o-mnie →
ls ~/artykuly/powiazane --limit 3
komentarze --count 12
rss dla komentarzy →
Julia2022-01-19 ·

A jakiego zapytania użyć jeśli chcemy imię, które na końcu nie ma litery a?

Łukasz Dudzińskiautor2022-01-23 ·

Hej Julia,

Musisz tutaj wykorzystać operator NOT.

Przykład:

SELECT * FROM Tabela WHERE Imie NOT LIKE '%a’;

Marcin2022-09-22 ·

Hej,

Uczę się dopiero, a jak np. wyszukać w kolumnie przy użyciu LIKE, że np. nie zawiera z 10 różnych warunków odnoszącyć się do tekstu? czyli not like 'zona’, 'brat’ itd…

Łukasz Dudzińskiautor2022-09-25 ·

Cześć Marcin,

Aby osiągnąć zamierzony efekt należy użyć słówka kluczowego AND (i). Poniżej przedstawiam przykład takiej kwerendy:

SELECT * FROM MyFamily WHERE Relationship NOT LIKE '%zona%' AND Relationship NOT LIKE '%brat%';

Zwróci ona wszystkie rekordy tabeli MyFamily gdzie kolumna Relationship nie będzie zawierała ciągu znaków „zona” oraz „brat”.

Pozdrawiam,
Łukasz Dudziński, autor bloga StrefaKodera.pl

PS: Zamiast AND (i) możesz użyć również OR (lub) – w zależności od tego w jaki sposób chcesz skonstruować warunek.

Wiktoria2022-10-24 ·

Jakiego zapytania użyć jeżeli chcemy wyszukać osobę, której nazwisko zaczyna się np na J a kończy na A?

Łukasz Dudzińskiautor2022-10-24 ·

Cześć Wiktoria,

Po prostu: LIKE 'J%A'

Pozdrawiam,
Łukasz

rom2023-09-07 ·

Witam,

jak napisać wykluczenie żeby nie zawiera ciąg znaków „kra” a wyświetlić wszystkie pozostałe?

Łukasz Dudzińskiautor2023-09-07 ·

Hej rom!

Wykorzystaj not:

SELECT * FROM tabela WHERE miasto NOT LIKE ‘%kra%’;

Dominik2023-11-07 ·

Cześć, mam podobny problem co użytkownik wyżej
„Hej,
Uczę się dopiero, a jak np. wyszukać w kolumnie przy użyciu LIKE, że np. nie zawiera z 10 różnych warunków odnoszącyć się do tekstu? czyli not like 'zona’, 'brat’ itd…”

Ale nie mam 10 tylko około 500-set takich warunków, czy jest sposób aby to wyszukać w szybszy sposób niż za każdym razem dodawać warunek or/and (w moim przypadku za każdym razem dodaje or) ?

Łukasz Dudzińskiautor2023-11-11 ·

Hej Dominik,

Z tego, co piszesz, to wydaje mi się, że ktoś popełnił błąd, projektując bazę danych – tutaj warto byłoby sobie poczytać o takim pojęciu jak „postać normalna”.

Jeśli natomiast chodzi o uproszczenie zapytania SQL, to potrzebowałbyś znaleźć jakiś wspólny „wzór” i takie warunki wykorzystać w klauzuli LIKE.

Pozdrawiam,
Łukasz Dudziński

arku2023-11-23 ·

A czy w przypadku dużej liczby warunków można zastosować słownik warunków/ wykluczeń w oddzielnej tabeli, która mogłaby być jakoś połączona z naszą tabelą źródłową? Przy tym ważne, by zachować możliwość stosowania znaków ? i %.

Łukasz Dudzińskiautor2023-11-23 ·

Hej Arku,

Da się to zrobić. Musisz wykorzystać zmienne. Do zmiennej przypisujesz wartość zapytania SELECT, którym „wyciągasz” dany argument, a następnie wykorzystujesz to w klauzuli LIKE:

SELECT * FROM Table WHERE Name LIKE = '%' + @myVariable + '%';

Pozdrawiam,
Łukasz Dudziński, autor bloga StrefaKodera.pl

dodaj-komentarz

Komentarze są moderowane. Podane dane przetwarzamy wyłącznie w celu publikacji komentarza (polityka prywatności).