Kolejna lekcja Darmowego Kursu SQL! Tym razem pobawimy się danymi, skupiając się na pojedynczych wierszach. Pokaże Ci jak funkcje SQL pozwalają programistom na modyfikowanie wyników wykonywanych zapytań. Lekcja ta zawiera listę najprzydatniejszych jednowierszowych funkcji SQL, wraz z praktycznymi przykładami ich użycia.
Jeżeli nie znasz pozostałych części kursu, zacznij od: Kurs SQL #01: Wprowadzenie i środowisko
Czym jest funkcja?
Pracujesz na kolumnie w której przechowywane są wprowadzone przez użytkowników serwisu nazwiska. Dane te każdy z użytkowników podaje przy zakładaniu konta. Istnieje wiele możliwości wprowadzenia nazwiska. Użytkownik może wprowadzić: „Kowalski”, „kowalski”, „KOWALSKI” lub nawet „kOWaLsKI”.
Oczywiście w idealnym świecie dane te powinny zostać poprawione do formy „Kowalski” już na poziomie samej aplikacji. Jeżeli jednak tak nie jest, a do bazy trafiają takie „kwiatki”, przy ich wyświetlaniu choćby na potrzeby raportów warto by poprawić i ujednolicić formę.
Funkcja jest niczym innym jak zapisanym w bazie danych kawałkiem kodu, który realizuje jakieś zadanie(algorytm). Takim zadaniem może być właśnie modyfikowanie ciągów znaków tak, aby pierwsza litera każdego słowa w ciągu była literą dużą.
Typy funkcji SQL
W języku SQL wyróżnia się dwa typy funkcji:
- Jednowierszowe – Funkcje te operują na jednym wierszu. Ich wynikiem zawsze jest również jeden wiersz. Funkcja od „nazwisk” jest właśnie funkcją jednowierszową. Wśród funkcji jednowierszowych występują na przykład funkcje SQL:
- znakowe – modyfikacja napisów
- numeryczne – wykonywanie obliczeń
- konwertujące – konwersja z jednego typu na inny
- datowe – przetwarzanie dat i czasu
- Agregujące – Funkcje te przyjmują na wejście wiele wierszy, efektem ich działania jest wynik w postaci pojedynczego wiersza. Przykładem może tu być funkcja sumującą wartości liczbowe z wielu wierszy i zwracająca wynik dodawania.
Popularne jednowierszowe funkcje SQL
Funkcja CONCAT()
Funkcja ta jest wymienna z „||” którego używaliśmy już w wcześniejszych lekcjach. Jako jej parametry podaje dwie kolumny, które mają być złączone.
select concat(cust_first_name, cust_last_name)
from oe.customers;| CONCAT(CUST_FIRST_NAME,CUST_LAST_NAME) |
|---|
| SivajiLandis |
| MammuttiPacino |
| EliaFawcett |
| IshwaryaRoberts |
Funkcja INITCAP()
Dotarliśmy do funkcji która pomoże nam rozwiązać problem z „nazwiskami”. Funkcja INITCAP() w każdym z słów w ciągu znaków zmienia pierwszą literę na dużą, a pozostałe na małe. Na potrzeby przykładu podałem bezpośrednio ciąg znaków do funkcji. Dane w naszej bazie testowej mają ładnie sformatowane nazwiska 😉
select initcap('kOWalSKI') from dual;| INITCAP(’KOWALSKI’) |
|---|
| Kowalski |
Funkcja INSTR()
Jedna z moich ulubionych funkcji, pod pewnymi względami nie robi nic wielkiego. W praktyce użyta z funkcją SUBTSR() o której jeszcze będzie mowa, daje duże możliwości. INSTR() służy do wyszukiwania ciągów znakowych w tekście. Wynik jej działania to numer pozycji na której znajduje się początek szukanego ciągu. Przyjmuje cztery parametry, dwa obowiązkowe i dwa opcjonalne:
- Kolumna w której szukamy
- Szukany ciąg znaków
- [opcjonalnie] Pozycja od której zaczynamy szukanie
- [opcjonalnie] Numer wystąpienia szukanego ciągu znaków
Najprostsza forma funkcji INSTR():
select cust_email, instr(cust_email, '@')
from oe.customers;| CUST_EMAIL | INSTR(CUST_EMAIL,’@’) |
|---|---|
| Sivaji.Landis@GOLDENEYE.EXAMPLE.COM | 14 |
| Mammutti.Pacino@GREBE.EXAMPLE.COM | 16 |
| Elia.Fawcett@JACANA.EXAMPLE.COM | 13 |
| Ishwarya.Roberts@LAPWING.EXAMPLE.COM | 17 |
Przykład powyżej pomija ostatnie parametry. Oznacza to że szukamy pierwszego wystąpienia. Policz od początku adresów email i sprawdź czy zwrócone numery rzeczywiście wskazują na szukaną małpę 😉
select cust_email, instr(cust_email, '.', 1,2)
from oe.customers;| CUST_EMAIL | INSTR(CUST_EMAIL,’.’,1,2) |
|---|---|
| Sivaji.Landis@GOLDENEYE.EXAMPLE.COM | 24 |
| Mammutti.Pacino@GREBE.EXAMPLE.COM | 22 |
| Elia.Fawcett@JACANA.EXAMPLE.COM | 20 |
| Ishwarya.Roberts@LAPWING.EXAMPLE.COM | 25 |
W powyższym przykładzie szukamy drugiego wystąpienia kropki, zaczynając szukanie od pierwszego znaku.
Funkcja LENGTH()
Prosta funkcja zwracająca ilość znaków w ciągu. Tutaj akurat nie ma nad czym się rozpisywać 😀
select cust_email, length(cust_email)
from oe.customers;| CUST_EMAIL | LENGTH(CUST_EMAIL) |
|---|---|
| Sivaji.Landis@GOLDENEYE.EXAMPLE.COM | 35 |
| Mammutti.Pacino@GREBE.EXAMPLE.COM | 33 |
| Elia.Fawcett@JACANA.EXAMPLE.COM | 31 |
| Ishwarya.Roberts@LAPWING.EXAMPLE.COM | 36 |
Funkcje UPPER() i LOWER()
Dwie funkcje modyfikujące ciąg znaków odpowiednio do dużych i małych liter.
select cust_first_name, upper(cust_first_name), lower(cust_first_name)
from oe.customers;| CUST_FIRST_NAME | UPPER(CUST_FIRST_NAME) | LOWER(CUST_FIRST_NAME) |
|---|---|---|
| Sivaji | SIVAJI | sivaji |
| Mammutti | MAMMUTTI | mammutti |
| Elia | ELIA | elia |
| Ishwarya | ISHWARYA | ishwarya |
Funkcje LPAD() i RPAD()
Funkcje te dopełniają ciąg znakowy. Użytkownik podaje do ilu znaków ma być rozszerzony wejściowy ciąg znakowy. Domyślnie funkcje te wykonują dopełnienie znakiem spacji, istnieje możliwość modyfikacji ciągu dopełniającego.
select cust_first_name, lpad(cust_first_name, 7, '*')
from oe.customers;| CUST_FIRST_NAME | LPAD(CUST_FIRST_NAME,7,’*’) |
|---|---|
| Sivaji | *Sivaji |
| Mammutti | Mammutt |
| Elia | ***Elia |
| Ishwarya | Ishwary |
Jak widać wynikiem operacji LPAD jest dopełnienie po lewej stronie(left). Ciąg znaków został dopełniony znakiem gwiazdki tak, aby sumarycznie był długości siedmiu znaków. RPAD() wykona identyczne dopełnienie po prawej stronie.
Pobaw się chwile dłużej RPAD() i LPAD() podstawiając różne ilości znaków i różne ciągi wypełnienia.
LTRIM(), RTRIM() i TRIM()
Funkcje te są odwrotnymi od LPAD() i RPAD(), zamiast dodawać znaki, usuwają je. Domyślnie usuwane są znaki spacji, opcjonalny parametr pozwala wybrać jakie ciągi znakowe mają być usunięte.
select 'oooooDBAdminooooo',
ltrim('oooooDBAdminooooo','o'),
rtrim('oooooDBAdminooooo','o'),
trim('o' FROM 'oooooDBAdminooooo')
from dual;| ’OOOOODBADMINOOOOO’ | LTRIM(’OOOOODBADMINOOOOO’,’O’) | RTRIM(’OOOOODBADMINOOOOO’,’O’) | TRIM(’O’FROM’OOOOODBADMINOOOOO’) |
|---|---|---|---|
| oooooDBAdminooooo | DBAdminooooo | oooooDBAdmin | DBAdmin |
Funkcja REPLACE()
Funkcja SQL może służyć także do podmieniania tekstu, tym właśnie zajmuje się REPLACE(). Warto przypomnieć: funkcje jednowierszowe nie modyfikują danych w bazie, a jedynie wynik zapytania. REPLACE() oprócz kolumny na której ma pracować, przyjmuje jako parametr szukany ciąg i ciąg który ma go zastąpić.
select cust_email, replace(cust_email,'GOLDENEYE.EXAMPLE.COM','GOOGLE.COM')
from oe.customers;| CUST_EMAIL | REPLACE(CUST_EMAIL,’GOLDENEYE.EXAMPLE.COM’,’GOOGLE.COM’) |
|---|---|
| Sivaji.Landis@GOLDENEYE.EXAMPLE.COM | Sivaji.Landis@GOOGLE.COM |
| Mammutti.Pacino@GREBE.EXAMPLE.COM | Mammutti.Pacino@GREBE.EXAMPLE.COM |
| Elia.Fawcett@JACANA.EXAMPLE.COM | Elia.Fawcett@JACANA.EXAMPLE.COM |
| Ishwarya.Roberts@LAPWING.EXAMPLE.COM | Ishwarya.Roberts@LAPWING.EXAMPLE.COM |
Funkcja SUBSTR()
Druga z moich ulubionych funkcji, tym razem pozwalająca zwrócić kawałek ciągu znaków wycięty z ciągu wejściowego. Jako parametr oprócz kolumny, przyjmuje pozycje z której ma rozpocząć wycinanie i ilość znaków które ma wyciąć. Jako ciekawostkę powiem Ci, że podanie ujemnej wartości dla pozycji startu spowoduje liczenie jej od końca ciągu znakowego 😉
select cust_email, substr(cust_email, 3, 3)
from oe.customers;| CUST_EMAIL | SUBSTR(CUST_EMAIL,3,3) |
|---|---|
| Sivaji.Landis@GOLDENEYE.EXAMPLE.COM | vaj |
| Mammutti.Pacino@GREBE.EXAMPLE.COM | mmu |
| Elia.Fawcett@JACANA.EXAMPLE.COM | ia. |
| Ishwarya.Roberts@LAPWING.EXAMPLE.COM | hwa |
Powyższy przykład pokazuje wycięcie trzech znaków zaczynając od trzeciego znaku w ciągu.
Łączenie funkcji SQL
Zanim pokaże Ci kolejne ciekawe funkcje, czas na pokaz jak łączyć funkcje SQL. Musisz mieć świadomość, że nic nie stoi na przeszkodzie, aby używać funkcji w funkcji… czy nawet jeszcze w kolejnej funkcji. Weźmy dwie moje ulubienice INSTR() i SUBSTR(). Załóżmy, że chcesz pozyskać listę domen z adresów mailowych. Trzeba zatem wykorzystać SUBSTR() do wycinania, INSTR() wskaże mu z której pozycji ma zacząć wycinanie. Brak podanej ilości znaków spowoduje wycięcie znaków do końca ciągu znakowego.
select cust_email, substr(cust_email,instr(cust_email,'@')+1)
from oe.customers;| CUST_EMAIL | SUBSTR(CUST_EMAIL,INSTR(CUST_EMAIL,’@’)+1) |
|---|---|
| Sivaji.Landis@GOLDENEYE.EXAMPLE.COM | GOLDENEYE.EXAMPLE.COM |
| Mammutti.Pacino@GREBE.EXAMPLE.COM | GREBE.EXAMPLE.COM |
| Elia.Fawcett@JACANA.EXAMPLE.COM | JACANA.EXAMPLE.COM |
| Ishwarya.Roberts@LAPWING.EXAMPLE.COM | LAPWING.EXAMPLE.COM |
Jeżeli zastanawiasz się po co +1, usuń je i wykonaj powyższe zapytanie ponownie 😉
Dobra to było za proste… wyciągnijmy tym razem tylko pierwszy człon domeny.
select
cust_email,
substr(cust_email,instr(cust_email,'@')+1,instr(cust_email,'.',instr(cust_email,'@'))-instr(cust_email,'@')-1)
from oe.customers;| CUST_EMAIL | SUBSTR(CUST_EMAIL,INSTR(CUST_EMAIL,’@’)+1,INSTR(CUST_EMAIL,’.’,INSTR(CUST_EMAIL,’@’))-INSTR(CUST_EMAIL,’@’)-1) |
|---|---|
| Sivaji.Landis@GOLDENEYE.EXAMPLE.COM | GOLDENEYE |
| Mammutti.Pacino@GREBE.EXAMPLE.COM | GREBE |
| Elia.Fawcett@JACANA.EXAMPLE.COM | JACANA |
| Ishwarya.Roberts@LAPWING.EXAMPLE.COM | LAPWING |
✅ Jak rozumiesz powyższe zapytanie? Opisz w komentarzu! ✅
Poznaj bazy danych Oracle od strony administracji i zarządzania nimi – przejdź kompleksowy kurs: Praktyczna Administracja Oracle Database!
Funkcja ABS()
Przechodzimy do pierwszej funkcji numerycznej. ABS() to prosta funkcja, która zwraca wartość bezwzględną.
select abs(10), abs(-10), abs(-5.5), abs(-2.2) from dual;| ABS(10) | ABS(-10) | ABS(-5.5) | ABS(-2.2) |
|---|---|---|---|
| 10 | 10 | 5.5 | 2.2 |
Funkcje CEIL() i FLOOR()
Dwie funkcje SQL które zwracają odpowiednio zaokrąglenie do góry(CEIL()) i do dołu(FLOOR()).
select ceil(2.2), ceil(-2.2), floor(2.2), floor(-2.2) from dual;| CEIL(2.2) | CEIL(-2.2) | FLOOR(2.2) | FLOOR(-2.2) |
|---|---|---|---|
| 3 | -2 | 2 | -3 |
Funkcje GREATEST() i LEAST()
Funkcje te operują na liście wartości. Funkcja GREATEST() zwróci zawsze największą wartość z listy, podczas gdy LEAST() najmniejszą.
select greatest(1,2,3,4), least(1,2,3,4) from dual;| GREATEST(1,2,3,4) | LEAST(1,2,3,4) |
|---|---|
| 4 | 1 |
Funkcja ROUND()
Funkcja ROUND() wykonuje tradycyjne zaokrąglenie liczby. Opcjonalnie jako drugi parametr możemy podać do ilu miejsc do przecinku chcemy zaokrąglać.
select round(2.55), round(2.55,1), round(-2.55), round(-2.55,1) from dual;| ROUND(2.55) | ROUND(2.55,1) | ROUND(-2.55) | ROUND(-2.55,1) |
|---|---|---|---|
| 3 | 2.6 | -3 | -2.6 |
Funkcje konwertujące i datowe
W tym poście pominę funkcje konwertujące typy danych i funkcje datowe, zajmiemy się nimi w kolejnej lekcji! Myśle że będzie to idealny moment, aby powiedzieć więcej o typach danych w bazach, a także o samej ich konwersji!
Część dziesiąta już dostępna! Kurs SQL #10: Typy danych i funkcje konwertujące