Kurs Oracle SQL online. Funkcje wbudowane

W tej lekcji pokażemy często wykorzystywane funkcje wbudowane – zarówno tekstowe jak i numeryczne. Czyli jak wybrać inicjały, jak zaokrąglić liczbę, jak poprawić wielkość liter. Każdą funkcję pokażemy wraz z przykładem użycia.

Zapraszam do lektury!

Do dzieła! Zacznijmy od popularnych funkcji numerycznych w Oracle SQL:

  • TRUNC(n1,n2) – obcina liczbę n1 do n2 miejsc dziesiętnych
SELECT Trunc(777.777,2),Trunc(777.777,0), Trunc(777.777), Trunc(777.777,-1)  FROM dual;

Wynik:

TRUNC(777.777,2) TRUNC(777.777,0) TRUNC(777.777) TRUNC(777.777,-1)
777,77 777 777 770
  • ROUND(n1,n2) – zaokrągla liczbę n1 do n2 miejsc dziesiętnych
SELECT Round(777.777,2),Round(777.777,0), Round(777.777), Round(777.777,-1)  FROM dual;

Wynik:

ROUND(777.777,2) ROUND(777.777,0) ROUND(777.777) ROUND(777.777,-1)
777,78 778 778 780
  • FLOOR(n1) – zwraca największą liczbę całkowitą mniejszą lub równą liczbie n1
SELECT Floor(777.77), Floor (-777.77), Floor(0) FROM dual;

Wynik:

FLOOR(777.77) FLOOR(-777.77) FLOOR(0)
777 -778 0
  • CEIL(n1) – zwraca najmniejszą liczbę całkowitą większą lub równą liczbie n1
SELECT Ceil(777.77), Ceil (-777.77), Ceil(0) FROM dual;

Wynik:

CEIL(777.77) CEIL(-777.77) CEIL(0)
778 -777 0
  • MOD(n1, n2) – modulo – zwraca resztę z dzielenia całkowitego n1 przez n2
SELECT Mod(10,3) FROM dual;

Wynik: 1

  • ABS(n1) – zwraca wartość bezwzględną
SELECT Abs(777.77), Abs(-777.77), Abs(0) FROM dual;

Wynik:

ABS(777.77) ABS(-777.77) ABS(0)
777,77 777,77 0
  • SIGN(n1) – zwraca 1 dla n1 dodatniego, -1 dla n1 ujemnego i 0 dla n1 równego 0

SELECT Sign(777.77), Sign(-777.77), Sign(0) FROM dual;

Wynik:

SIGN(777.77) SIGN(-777.77) SIGN(0)
1 -1 0
  • SQRT(n1) – zwraca pierwiastek kwadratowy z n1
SELECT Sqrt(9) FROM dual;

Wynik: 3

  • POWER(n1, n2) – zwraca n1 podniesioną do potęgi n2
SELECT Power(2,3) FROM dual;

Wynik: 8

 

Kolej na popularne funkcje tekstowe:

  • LOWER(ciąg_znaków) – zamienia wszystkie znaki na małe
  • UPPER(ciąg_znaków) – zamienia wszystkie znaki na wielkie

SELECT first_name, Upper(first_name), Lower(first_name) FROM employees;

Wynik:

FIRST_NAME UPPER(FIRST_NAME) LOWER(FIRST_NAME)
Ellen ELLEN ellen
Sundar SUNDAR sundar

 

  • INITCAP(ciąg_znaków) – zamiana pierwszej litery w każdym słowie z ciągu na wielką,   pozostałe na małe
SELECT InitCap('jan kowalski') FROM dual;

Wynik: Jan Kowalski

  • LPAD(ciąg_znaków_1, n1 [, ciąg_znaków_2]) – zwraca ciąg znaków od długości n1 utworzony przez dodanie z lewej strony znaków z ciag_znakow_2. W przypadku braku ciąg_znaków_2 dodawane są spacje.
SELECT LPad('Ala',6,'|') FROM dual;

Wynik: |||Ala

  • RPAD(ciąg_znaków_1, n1 [, ciąg_znaków_2]) – działa analogicznie jak LPAD dodając znaki z prawej strony
SELECT RPad('Ala',6,'|') FROM dual;

Wynik: Ala|||

  • LTRIM(ciąg_znaków_1 [,ciąg_znaków_2]) – usuwa z ciąg_znaków_1 z lewej strony znaki zawarte w ciąg_znaków_2. W przypadku braku ciąg_znaków_2 usuwane są spacje.
  • RTRIM(ciąg_znaków_1 [,ciąg_znaków_2]) – działa analogicznie jak LTRIM usuwając znaki z prawej strony.
SELECT LTrim('|||Ala','|'), RTrim('Ala|||','|') FROM dual;

Wynik:

LTRIM(‘|||ALA’,’|’) RTRIM(‘ALA|||’,’|’)
Ala Ala

Najczęściej funkcje LTRIM i RTRIM używane są do usuwania spacji na początku i na końcu ciągu tekstowego.

  • TRIM(ciąg_znaków_1) – usuwa z ciąg_znaków_1 spacje – zarówno z lewej jak i z prawej strony
SELECT Trim('  Ala  ') FROM dual;

Wynik: Ala

  • SUBSTR(ciąg_znaków_1, m [, n]) – wybiera z ciąg_ znaków n znaków od m pozycji.
    W przypadku braku n wybiera znaki do końca.

Wybierzmy inicjały pracowników 🙂

SELECT SubStr(first_name,1,1)||SubStr(last_name,1,1),first_name, last_name
FROM employees;

Wynik:

SUBSTR(FIRST_NAME,1,1)||SUBSTR(LAST_NAME,1,1) FIRST_NAME LAST_NAME
EA Ellen Abel
SA Sundar Ande
..
  • INSTR(ciąg_znaków_1,ciąg_znaków_2 [,m, n]) – szuka n-tego wystąpienia ciągu2 w ciągu1 rozpoczynając od pozycji m. Domyślnie m i n wynoszą

Wyszukajmy pozycję pierwszej litery ‘e’ w last_name – szukając od pierwszego znaku

SELECT instr(last_name,'e',1,1),last_name FROM employees;

Wynik:

INSTR(LAST_NAME,’E’,1,1) LAST_NAME
3 Abel
4 Ande
  • LENGTH(ciąg_znaków_1) – zwraca długość ciągu znaków
SELECT first_name, Length(first_name) FROM employees;

Wynik:

FIRST_NAME LENGTH(FIRST_NAME)
Ellen 5
Sundar 6
  • REPLACE(ciąg_znaków1, ciąg_znaków2 [, ciąg_znaków3]) – zamienia wszystkie wystąpienia ciąg_2 w ciąg_1 na ciąg_3. W przypadku braku ciąg_3 zamienia na znak pusty

Zamieńmy wystąpienia litery ‘e’ w last_name na słowo TEST

SELECT replace(last_name,'e','TEST'),last_name FROM employees;

Wynik:

REPLACE(LAST_NAME,’E’,’TEST’) LAST_NAME
AbTESTl Abel
AndTEST Ande
  • TRANSLATE(ciąg_znaków_1, ciąg_znaków_2, ciąg_znaków_3) – zamienia znaki na odpowiednich pozycjach z ciąg_1 na znaki na odpowiednich pozycjach w ciąg_2

W kolumnie last_name zamieńmy wystąpienia litery ‘A’ na ‘X’, litery ‘e’ na ‘Y’

SELECT translate(last_name,'Ae','XY'),last_name FROM employees;

Wynik:

TRANSLATE(LAST_NAME,’AE’,’XY’) LAST_NAME
XbYl Abel
XndY Ande

Co w następnej lekcji?

Coś co jest bardzo ważne w każdym projekcie, czyli obsługa dat 🙂

TRUNC(777.777,2) TRUNC(777.777,0) TRUNC(777.777) TRUNC(777.777,-1)
777,77 777 777 770

About Rafal Stryjek

My LinkedIn profile. Passionate about data. Data Warehousing Expert. Snowflake Data SuperHero & Oracle ACE Associate Alumni. International conferences Speaker: Snowflake Build Summit-BUILD.local 2021, Oracle Open World, DOAG, UKOUG, APEX World,.. Visit dataconsulting.pl website!
This entry was posted in Oracle SQL, Oracle SQL Podstawy, SQL and tagged , , , . Bookmark the permalink.

Leave a Reply