Jak filtrować i przekształcać napisy w zapytaniach SQL? – lekcja 17

Wstęp

Jak w klauzuli WHERE filtrować wiersze na podstawie kolumn zawierających łańcuchy znaków oraz jak łańcuchy znaków przekształcać dowiesz się z niniejszej lekcji. Zaprezentuję kilka najpopularniejszych funkcji przetwarzających dane w postaci łańcuchów znaków. Każda z nich może być użyta zarówno w klauzuli WHERE jak i SELECT. Zaprezentuję także operatory LIKE i konkatenacji oraz wspomnę o zawiłościach w obsłudze znaków narodowych i NULL.

Najprościej

Najprościej wyszukasz potrzebne dane, przyrównując w klauzuli WHERE kolumnę typu znakowego do literału. Łańcuch znaków, który przyrównujesz musi być zamknięty w apostrofach.

SELECT
  table_name
FROM
  all_tables
WHERE
  tablespace_name='SYSTEM';

Wynikiem jest lista tabel utworzonych w tablespace o nazwie 'SYSTEM’. Zwróć uwagę, że napis 'SYSTEM’ podałem wielkimi literami. Jak zapewne wiesz w słowniku Oracle nazwy obiektów przechowywane są dużymi literami. No chyba, że zostały utworzone małymi w cudzysłowie.

Bardzo ważne jest aby pamiętać jak Oracle traktuje pusty łańcuch znaków. Dla Oracle, pusty łańcuch znaków, jest NULL.

SELECT
  'Pusty łańcuch znaków jest NULL.'
FROM
  dual
WHERE
  '' is null;

Jest to zachowanie różne od innych motorów baz danych. O tym co to jest NULL mówiłem we wcześniejszej lekcji.

Operator LIKE

Gdy znasz jedynie fragment poszukiwanego łańcucha znaków możesz użyć operatora LIKE. Pozwala na przeszukiwanie zawartości kolumn według zadanego wzorca. Wzorzec budujesz wykorzystując znany fragment napisu oraz dwa wieloznaczniki:

• procent '%’
• podkreślnik '_’

Procent zastępuje od zera do wielu znaków, a podkreślnik dokładnie jeden znak. Funkcjonalnie przypominają gwiazdkę i znak zapytania z systemów operacyjnych.

Trzeba pamiętać, że operator LIKE jest wrażliwy na wielkość liter. Wieloznaczniki mogą pojawić się w dowolnym miejscu i więcej niż raz. Na przykład wyszukanie wszystkich kolumn dowolnego typu znakowego.

SELECT
  column_name
  ,data_type
FROM
  all_tab_columns
WHERE
  data_type LIKE '%CHAR%';

Jeśli na przeszukiwanej kolumnie istnieje indeks to wieloznacznik użyty jako pierwszy znak we wzorcu uniemożliwi jego wykorzystanie. Im więcej znaków wpiszesz przed wieloznacznikiem tym większa szansa, że Oracle użyje indeksu.

Jeśli znak procent lub podkreślnik jest częścią wzorca i nie chcesz aby był interpretowany jako wieloznacznik to możesz go zamaskować. Znak maskujący wieloznacznik definiujesz słowem kluczowym ESCAPE.

SELECT
  table_name
FROM
  all_tab_columns
WHERE
  table_name LIKE 'USER!_%' ESCAPE '!';

Czyli po operatorze LIKE i wzorcu ujętym w apostrofy umieszczasz słowo kluczowe ESCAPE, a następnie znak maskujący otoczony apostrofami. Takie zapytanie wyszuka nazwy wszystkich kolumn z tabel, których nazwy zaczynają się na USER_. Specjalna funkcja podkreślnika została zamaskowana wykrzyknikiem.

Operatora LIKE możesz także używać na danych numerycznych i datoczasowych.

SELECT
  * 
FROM
  all_objects 
WHERE
  created LIKE '2%'
  OR object_id LIKE '3%';

Wtedy motor bazy danych dokona niejawnej konwersji do łańcucha znaków. Oprócz degradacji wydajności możesz otrzymać wyniki nie spełniające Twoich oczekiwań. Niejawna konwersja zostanie dokonana według ustawień narodowych sesji. Będzie o tym więcej w lekcjach prezentujących wybieranie danych datoczasowych i numerycznych.

Funkcja REGEXP_LIKE

Funkcja REGEXP_LIKE może być użyta zamiast operatora LIKE. Pozwala na wykorzystanie wyrażeń regularnych do budowy wzorców dopasowań. Wyrażenia regularne pozwalają na budowę bardziej skomplikowanych wzorców dopasowań niż wieloznaczniki wykorzystywane w LIKE.

SELECT
  * 
FROM
  all_objects 
WHERE
  regexp_like(object_name, '[a-m]$','c');

• pierwszy parametr to łańcuch znaków do przeszukania.
• drugi to wzorzec zbudowany według reguł wyrażeń regularnych.
• trzeci definiuje zasady na jakich dopasowanie ma przebiegać. Na przykład mała litera 'c’ oznacza, wrażliwość na wielkość liter.

Czyli zapytanie wyszuka wszystkie obiekty mające małą literę z zakresu od 'a’ do 'm’ na końcu nazwy. Znak dolara oznacza koniec łańcucha znaków.

Wyrażenia regularne i ich obsługa w bazie danych Oracle są tematem tak obszernym, że zasługującym na dedykowany materiał. Wykorzystaj komentarze aby namówić mnie do jego realizacji. Tymczasem tutaj możesz znaleźć listę funkcji wykorzystujących wyrażenia regularne.

Funkcje przekształcające

Gdy potrzebujesz aby wszystkie litery w napisie były małe lub duże użyj funkcji odpowiednio lower lub upper.

SELECT
  lower('marcin badtke') "wszystkie litery małe"
  ,upper('marcin badtke') "wszystkie litery duże"
  ,initcap('marcin badtke') "pierwsza litera duża"
FROM
  dual;

Natomiast initcap zamieni pierwszą literę w każdym słowie napisu na wielką.

Funkcje dopełniające łańcuch znaków wskazanym wyrażeniem do określonej długości z lewej lub prawej to lpad i rpad.

SELECT 
  lpad('Marcin',15,'*.') "dopełnione z lewej"
  ,rpad('Marcin',15,'*.') "dopełnione z prawej"
FROM
  dual;

Dopełnią łańcuch znaków z pierwszego argumentu do długości określonej drugim argumentem odpowiednio z lewej lub prawej. Jak nie podasz trzeciego argumentu to motor bazy danych dopełni spacjami. Zwróć uwagę, że dopełnienie może składać się z wielu znaków.

Ltrim i rtrim działają przeciwnie. Obcinają wskazane znaki odpowiednio z lewej lub prawej.

SELECT
  ltrim('<=====>MARCIN<=====>', '<>=') "obcięte z lewej"
  ,rtrim('<=====>MARCIN<=====>', '<>=') "obcięte z prawej"
FROM
  dual;

Oracle usunie wszystkie znaki wskazane w wyrażeniu podanym jako drugi argument. Gdy go nie podasz usunie spacje.

Pochodząca ze standardu ANSI funkcja trim użyta tylko z jednym parametrem – łańcuchem znaków – usunie spacje zarówno z początku wskazanego łańcucha jak i z końca.

SELECT
  trim('  MARCIN ' ) "spacje z obu stron"
  ,trim('-' FROM '-  MARCIN -' ) "- z obu stron"
  ,trim(BOTH '-' FROM '-  MARCIN -' ) "też z obu stron"
  ,trim(LEADING '-' FROM '-  MARCIN -' ) "- z początku"
  ,trim(TRAILING '-' FROM '-  MARCIN -' ) "- z końca"
FROM
  dual;

Słowo kluczowe BOTH lub jego brak powoduje usunięcie znaku wskazanego pierwszym parametrem z obu końców napisu z drugiego parametru. LEADING usuwa początkowe czyli z lewej strony. TRAILING końcowe czyli z prawej strony. Ważne aby zapamiętać, że możesz wskazać tylko jeden znak.

Gdy chcesz wyciąć kawałek łańcucha znaków używasz funkcji substr.

SELECT
  substr('marcin', 4, 3) "od poczatku"
  ,substr('marcin', -3, 3) "od końca"
FROM
  dual;

• pierwszym argumentem jest napis.
• drugim numer znaku od którego rozpocząć wycinanie. Zero i jeden oznaczają pierwszy znak. Jeśli numer znaku jest ujemny to Oracle policzy od końca.
• trzecim argumentem jest ilość znaków do wycięcia. Gdy trzeciego argumentu nie ma to zwrócone zostaną wszystkie znaki aż do końca. Gdy trzeci argument jest mniejszy niż jeden to zwrócony zostanie NULL.

Czasem chcesz się dowiedzieć na jakiej pozycji w łańcuchu znaków zaczyna się poszukiwana wartość. Wtedy użyj funkcji instr.

SELECT
  instr('Marcin Badtke', 'a', 4, 1) "od początku"
  ,instr('Marcin Badtke', 'a', -3, 2) "od końca"
FROM
  dual;

• pierwszym argumentem jest napis do przeszukania.
• drugim wyrażenie, którego szukasz. Może zawierać więcej niż jeden znak.
• trzecim argumentem wskazujesz od jakiej pozycji zacząć szukać. Liczba ujemna nakazuje liczyć od końca.
• czwartym argumentem wskazujesz, które wystąpienie poszukiwanych znaków Cię interesuje. Gdy pominiesz dwa ostatnie argumenty Oracle będzie szukać pierwszego wystąpienia licząc od początku łańcucha znaków.

Ostatnimi funkcjami na które zwracam Twoją uwagę są length, ascii i chr.

SELECT
  length('Marcin Badtke') "ilość znaków"
  ,ascii('Marcin') "kod ASCII pierwszego znaku"
  ,chr(67) "znak o kodzie 67"
FROM
  dual;

Każda z funkcji przyjmuje jeden argument.

• length liczy długość łańcucha znaków w znakach.
• ascii zwraca kod ASCII wskazanego znaku. Jak parametr będzie dłuższy niż jeden znak – jak mój – to zwróci kod pierwszego.
• chr odwrotnie, zwraca znak o podanym kodzie. U mnie zwraca literę duże 'C’. U Ciebie może coś innego w zależności jaki zestaw znaków ma ustawiony Twoja baza danych.

Argumentami funkcji przekształcających napisy mogą być także dane typów datoczasowych czy numerycznych. Wtedy zostaną niejawnie skonwertowane do danych znakowych. Więcej funkcji znajdziesz w dokumentacji.

Warto pamiętać, że funkcje można zagnieżdżać. Czyli przekazywać wynik jednej jako argument drugiej.

SELECT
  upper(substr('Marcin Badtke',instr('Marcin Badtke', ' '))) "nazwisko wielkimi"
FROM
  dual;

Znaki narodowe

Operatorem COLLATE użytym w klauzuli ORDER BY możesz wymusić sortowanie według polskich reguł.

SELECT
  table_name
FROM
  all_tables
ORDER BY table_name COLLATE polish;

Nawet jeśli baza danych, instancja, sesja czy schemat tabeli mają inne ustawienia. Przydatne jeśli Twoja baza danych przechowuje dane w różnych językach. Wtedy przydatne mogą być także narodowe odpowiedniki funkcji lower, upper czy initcap. Cechują się przedrostkiem NLS_. Pierwszym argumentem wskazujesz napis do przekształcenia, a drugim reguły narodowe według jakich ma to nastąpić.

Komentarzami możesz namówić mnie do zrobienia dedykowanego materiału na temat obsługi wielu języków w jednej bazie danych. Tymczasem pełną listę funkcji NLS oraz ich omówienie znajdziesz w dokumentacji.

Konkatenacja

W Oracle do sklejania napisów służy operator dwie pałki oraz funkcja concat. Dzięki pałkom możesz skleić wiele wartości w jeden ciąg. Natomiast funkcja concat przyjmuje tylko dwa argumenty. Niemniej można zagnieżdżać jej wywołania.

SELECT
  'Kurs '||'Marcina Badtke:'||' SQL Oracle od podstaw'
  ,concat(concat('Kurs',' Marcina Badtke:'),' SQL Oracle od podstaw')
FROM
  dual;

Skoro łańcuch pusty jest NULL, a pamiętasz, że cokolwiek użyte z NULL zwraca NULL, to co będzie jak skleisz napis z łańcuchem pustym?

To świetne pytanie. W Oracle gdy sklejasz łańcuch znaków z NULL w efekcie dostaniesz łańcuch znaków. Niezależnie czy używasz operatora czy funkcji concat.

SELECT
  ''||'Marcin Badtke'||NULL
  ,concat('',concat('Marcin Badtke',NULL))
FROM
  dual;

W innych motorach baz danych jest inaczej. NULL doklejony do łańcucha znaków może zwrócić NULL.

Trzeba pamiętać, że dane typów datoczasowych i numerycznych, w momencie sklejenia, zostaną niejawnie skonwertowane do typu znakowego. W związku z tym zostaną sformatowane według ustawień narodowych Twojej sesji. O czym będę mówił w następnych lekcjach.

Wydajność

Każdej z funkcji przekształcającej łańcuchy znaków możesz użyć w klauzuli WHERE. Pamiętaj, że użycie operatora LIKE czy funkcji na kolumnie może uniemożliwić optymalizatorowi wykorzystanie indeksu zbudowanego na tej kolumnie. Niemniej są metody na obejście tej niedogodności. Jeśli chcesz więcej informacji na ten temat to namów mnie w komentarzu do zrobienia dedykowanego materiału. Tymczasem zapamiętaj jedynie, że zawsze lepszym pomysłem jest użycie funkcji na literale zamiast na kolumnie.

Funkcja użyta na kolumnie będzie uruchomiona dla każdego rekordu. Natomiast użyta na literale zostanie wykonana tylko raz.

Warto mieć także na uwadze, że motor bazy danych, aby zwrócić dane do klienta musi pobrać je z dysku i zapisać w pamięci. Następnie przepisać do struktury, którą będzie mógł transmitować przez sieć. Dodanie dodatkowego przetwarzania w postaci modyfikacji danych może być mniej kosztowne niż przetwarzanie po stronie klienta.

Podsumowanie

Gratuluję Ci ukończenia siedemnastej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, jak używać operatorów i funkcji do filtrowania i przekształcania łańcuchów znaków. Wiesz także na co zwrócić uwagę aby wyszukiwanie danych było efektywne.

Zadanie dla Ciebie: napisz zapytanie do widoku all_tables zwracające sklejone kolumny owner i table_name. Pomiędzy kolumny wstaw kropkę. Wybierz wiersze dla których kolumna tablespace_name zawiera wartość inną niż SYS lub SYSTEM. Wynik posortuj rosnąco.

Jeśli potrzebujesz podpowiedzi wbijaj na discord. Tam też chwal się swoim rozwiązaniem prezentując zrzut ekranu swojego klienta. Dedykowany serwer: Marcin Badtke – bazy danych i SQL.

Wiesz już jak używać operatora LIKE oraz funkcji regexp_like do filtrowania danych znakowych. Wiesz także jak wykorzystać funkcje przekształcające do pracy z łańcuchami znaków. W następnej lekcji zaprezentuję wybieranie punktów w czasie.

Tymczasem dziękuję za ukończenie niniejszej.

Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Wróć do poprzedniej lekcji