W tym kursie przedstawię jak w bazie danych Oracle wyszukiwać wzorce w tekście przy użyciu w SQL LIKE, NOT LIKE, ESCAPE. Pokażę zastosowanie każdego z ograniczeń. Przedstawię różnice między nimi oraz między operatorem “=”. Do każdego zastosowania przedstawię przykłady oraz opiszę zasadę działania oraz praktyczne zastosowanie. Przykładów będzie naprawdę dużo 🙂
Po tym kursie poznasz:
- Składnię LIKE w SQL
- Działanie SQL LIKE
- Pokaże wykorzystanie we wzorcu LIKE znaku %
- Pokaże wykorzystanie we wzorcu LIKE znaku _
- Pokażę zastosowanie wszystkich znaków specjalnych we wzorcu
- Pokaże zastosowanie klauzuli SQL LIKE ESCAPE
- W tym błąd ORA-01425
- Pokażę klauzulę zaprzeczenia czyli NOT LIKE
- Oraz podsumuję cały kurs
Wszystko pokażę na przykładzie bazy danych stworzonej specjalnie do tego kursu.
Ten kurs jest częścią cyklu Kurs SQL
Przykładowa baza danych
Aby rozpocząć kurs należy przygotować prostą bazę danych wraz z danymi. Do wyszukiwania tekstu wystarczy baza z jedną tabelą oraz kolumną typu varchar2. Utwórzmy więc tabelę zawierającą nazwy województw oraz ich hasła reklamowe.
Przykładowy SQL tworzący tabelę może wyglądać jak poniżej:
CREATE TABLE WOJEWODZTWA (
ID NUMBER GENERATED ALWAYS AS IDENTITY
, NAZWA VARCHAR2(100 CHAR)
, HASLO VARCHAR2(2000 CHAR)
);oraz wypełnijmy ją danymi:
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Dolnośląskie', 'Dolny Śląsk. Nie do opowiedzenia. Do zobaczenia');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Kujawsko-Pomorskie', ' Kujawsko-Pomorskie. Lubię tu być');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Mazowieckie', 'Mazowsze. Serce Polski');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Lubelskie', 'Lubelskie Smakuj życie!');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Lubuskie', 'Lubuskie warte zachodu');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Łódzkie', 'Łódzkie nabiera prędkości');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Małopolskie', 'Odkryj Małopolskę');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Opolskie', 'Opolskie kwitnące');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Podkarpackie', 'Podkarpackie przestrzeń otwarta');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Podlaskie', ' Podlaskie bogactwo różnorodności');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Pomorskie', 'Pomorskie Życia Pełne');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Śląskie', 'Śląskie. Pozytywna Energia');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Świętokrzyskie', 'Świętokrzyskie czaruje');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Warmińsko-Mazurskie', 'Mazury cud natury');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Wielkopolskie', ' Wielkopolska. wielka historia, wielka przygoda');
INSERT INTO WOJEWODZTWA (NAZWA, HASLO) VALUES ('Zachodniopomorskie', 'Zachodniopomorskie. Morze przygody');
COMMIT;Skoro mamy przygotowaną tabelę możemy przejść do rozpoczęcia testów. A rozpoczniemy od klauzuli LIKE.
SQL LIKE
Warunek WHERE LIKE określa dopasowanie wzorców. LIKE dopasowuje część jednego ciągu znakowego do drugiego, dopasowując pierwszą część pod kątem wystąpienia wzorca w drugim ciągu znaków. W przeciwieństwie do operatora równoważności “=” ciągu w LIKE nie muszą być dokładnie takie same w ramach kolumny a wystarczy, że zgadzać się będzie wzorzec. Klauzulę LIKE stosujemy więc do ograniczania wyników więc występować musi w sekcji WHERE
Uwaga: w SQL LIKE jest czułe na wielkość liter.
Składnia LIKE
LIKE wykorzystywany jest w SQL w sekcji ograniczającej wyniki zapytania czyli WHERE. Składnia może wyglądać jak pniżej:
SELECT
*
FROM TABELA
WHERE nazwa_kolumny LIKE 'wzorzec';Tak więc na składnię like składają się trzy elementy: kolumna w której szukamy wzorca następnie słowo kluczowe LIKE oraz wzorzec. Jeżeli zawartość kolumny spełnia wzorzec to zostaje zwrócony do wyniku zapytania.
Nim poznamy przykładowe wykorzystanie operacji LIKE musimy poznać charakterystykę budowy wzorca. Wzorzec składa się z ciągu znaków który chcemy dopasować oraz znaków specjalnych. We wzorcu występują dwa znaki specjalne:
- “_” znak podkreślenia jest miejscem w którym występować może dokładnie jeden dowolny znak.
- “%” znak procentu czyli dowolnego ciąg znaków. Na tyle dowolnego, że zawierającego dowolne znaki i dowolną długości tak, że dopasowuje również pusty ciąg lub jego brak.
Przykłady SQL LIKE
Mając przygotowaną bazę, znając działanie i składnię operatora LIKE napiszmy kilka przykładowych zapytań aby w praktyce zobaczyć działanie LIKE w SQL.
SQL LIKE %
Na wstępie rozpatrzmy przykłady z wykorzystaniem znaku specjalnego ‘%’ czyli dowolnego ciągu znaków.
Rozważmy więc w zapytanie które zwróci nam wszystkie województwa które zaczynają się na ciąg “Lub”. Przykładowe zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE 'Lub%';oraz wynik:
Jak widzimy na powyższym przykładzie wzorzec ‘Lub%’ określił wszystkie województwa w których nazwa zaczyna się na litery ‘Lub’ a następnie dopasowuje dowolne znaki poprzez wykorzystanie znaku ‘%’.
Napiszmy więc podobne zapytanie ale które zwróci wszystkie województwa które kończą się na ciąg znaków “skie”. Przykładowe zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE '%skie';oraz wynik:
Jak widzimy, powyższe zapytanie zwróciło wszystkie województwa które kończą się na ciąg znaków ‘skie’. Dzięki zastosowaniu operatora % na początku wzorca baza nie sprawdza w ogóle pierwszych znaków a jedynie ostatnie cztery znaki.
Spróbujemy więc znaleźć teraz wszystkie województwa które mają znak “-” w nazwie. Zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE '%-%';oraz wynik:
Działanie wzorca ‘%-%’ jest takie, że przed jak i po znaku “-” mogą wystąpić dowolne znaki.
A teraz napiszmy wzorzec który wyszuka wszystkie województwa zaczynające się na literę “P” oraz kończące się na literę ‘e’. Przykładowe zapytanie SQL może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE 'P%e';oraz wynik:
Tak więc wzorzec wyszukuje w pierwszej kolejności litery ‘P’, następnie pomija wszystkie litery aż znajdzie na końcu znak ‘e’
Kolejnym przykładem na wykorzystanie operatora LIKE i znaku specjalnego ‘%‘ będzie wyszukanie wszystkich województw w których hasło zaczynające się na literę L, w środku posiadające literę e. Przykładowe zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE 'L%e%';oraz wynik:
Jak widzimy zapytanie poprawnie zwróciło wynik.
SQL LIKE _
Kolejnym znakiem specjalnym jest ‘_’ czyli pojedynczy dowolny znak.
Znajdźmy więc województwo o nazwie zaczynającej się na ciąg ‘Po’, następnie trzy dowolne znaki i kończące się na ‘skie’. Przykłądowe zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE 'Po___skie';oraz wynik:
Wzorzec Po___skie zawiera w środku 3 podkreślniki “_” czyli trzy dowolne znaki.
Zobaczmy więc które województwo zaczyna się na ‘Lub’ i składa się jeszcze z pięciu znaków? Przykładowe zapytanie:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE 'Lub_____';oraz wynik:
Jak widzimy, wzorzec z zapytania SQL spełnia tylko jedno województwo Lubelskie.
Znaki specjalne można łączyć w przeróżne ciągi. Dlatego też spróbujmy napisać zapytanie gdzie jeden pierwszy znak będzie dowolny, drugim będzie litera ‘o’ a reszta będzie bez znaczenia. Przykładowe zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE '_o%';
Jak widzimy warunek ten spełniają aż 4 województwa.
SQL LIKE ESCAPE
Wiemy, że znakami specjalnymi są znaki podkreślenia “_” oraz znak procentów “%”. Nasuwa się więc pytanie jak wyszukać ciągów w których występują dokładnie te znaki. Z pomocą przychodzi tu klauzula w SQL LIKE nazwana ESCAPE. Słowo kluczowe ESCAPE wyłącza z wzorca znaki które występują po znaku ESCAPE. ESCAPE przyjmuje jako parametr jeden znak. Do celów testowych dodajmy województwo o nazwie ‘AA%Lubu___kie’ poprzez:
INSERT INTO WOJEWODZTWA (NAZWA) VALUES ('AA%Lubu___kie');
COMMIT;A teraz napiszmy zapytanie które zwróci województwo w którym występuje ciąg trzech podkreślników. Przykładowe zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE '%\_\_\_%' escape '\';oraz wynik:
Jak widzimy w wzorcu najpeirw występuje znak % oznaczający dowolny ciąg. Następnie mamy znak “\” który informuje nas, że kolejny znak ma być traktowany jako znak podstawowy a nie znak specjalny. Taka sekwencja występuje trzy razy a następnie znak % mówi o dowolności ciągu znaków.
Poszukajmy więc jeszcze nazwy w której występuje ciąg ‘AA%L. Przykładowe zapytanie może wyglądać jak poniżej:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE '%AAX%L%' escape 'X';oraz wynik:
Jak widać, wynik został zwrócony poprawnie jednak sam wzorzec nie wygląda oczywiście. Głównie za sprawą zastosowania jako znaku ESCAPE znaku ‘X’. Oznacza to, że znak po ‘X’ określony we wzorcu ma być traktowany jako zwykły znak.
Uwaga: W starczych wersjach dokumentacji można znaleźć, że w SQL LIKE operatorem w ESCAPE jest znak ‘\’. Nie jest to prawda i jako znak escape można podać dowolny znak. Jednak dla czytelności zaleca zapytań zalecam korzystanie z nzaku ‘\’. Na pewno nie ze znaków alfabetu jak w przypadku z Xem 🙂
A co się stanie jak w Escape podamy więcej znaków niż jeden? Przykładowe zapytanie:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA LIKE '%\_\_\_%' escape '\\';W powyższej sytuacji baza danych zwróci błąd ORA-01425 o treści:
ORA-01425: symbol sekwencji sterującej musi być napisem znakowym o długości 1 00000 - "escape character must be character string of length 1" *Cause: Given escape character for LIKE is not a character string of length 1. *Action: Change it to a character string of length 1.
SQL NOT LIKE
Przeciwieństwem warunku LIKE jest jego negacja czyli warunek NOT LIKE. Warunek NOT LIKE w SQL znajduje wszystkie wyniki które nie pasują do wzorca. Napiszmy więc proste zapytanie w którym poszukamy wszystkich województw które nie zaczynają się na literę ‘P’:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND NAZWA NOT LIKE 'P%';oraz wynik
Jak widzimy trzynaście województw nie zaczyna się na literę ‘P’.
Najdźmy więc hasło województwa w którym nie występuje znak kropki ‘.’ przynajmniej raz. Przykładowe zapytanie:
SELECT
*
FROM WOJEWODZTWA
WHERE 1 = 1
AND HASLO NOT LIKE '%.%';oraz wynik:
Podsumowanie
- Klauzula SQL LIKE służy do wyszukiwania wzorców znaków
- Klauzulę LIKE wykorzystuje się w sekcji WHERE
- znak % we wzorcu oznacza dowolny ciąg znaków
- znak _ we wzorcu oznacza jeden znak w ciągu
- ESCAPE służy do oznacza znaków specjalnych jako zwykłych
- Klauzula NOT LIKE służy do wyszukiwania ciągów nie pasujących do wzorca