sobota, 6 listopada 2010

Kurs Oracle SQL . Funkcje znakowe


Funkcje znakowe


Upper


Zwiększa cały ciąg tekstowy. Jak widać na poniższym obrazku funkcja upper została zastosowana do zwiększenia wszystkich liter w wyniku.



Lower

Lower – zmniejsza wszystkie litery.




Initcap


Initcap - zwiększa pierwsze litery wszystkich wyrazów z wyniku.


Lpad


Lpad – funkcja uzupełniająca z lewej strony wynik (w tym wypadku zawartość kolumny department_name) znakami podanymi jako trzeci argument w taki sposób by wynik osiągnął długość podaną jako parametr 2. Jeśli wynik jest dłuższy niż zdeklarowana długość, zostaje ucięty z prawej strony.




Rpad


rpad – funkcja uzupełniająca z prawej strony wynik (w tym wypadku zawartość kolumny department_name) znakami podanymi jako trzeci argument w taki sposób by wynik osiągnął długość podaną jako parametr 2. Jeśli wynik jest dłuższy niż zdeklarowana długość, zostaje ucięty z prawej strony.




Replace


Replace (text, text1 [,text2]) – funkcja podmienia wszystkie wystąpienia ciągu text1 w ciągu text na text2. W przypadku braku ciągu text2 z ciągu text
usuwane są wszystkie wystąpienia ciągu text1.



Translate



translate (text, text1 ,text2) – funkcja działa podobnie jak funkcja replace, tylko że zamienia wszystkie wystąpienia pojedynczych liter z ciągu text1 na odpowiednie litery z ciągu text2.


Substr


substr (text, m [,n]) – wycina z ciągu text n znaków począwszy od pozycji m. Jeżeli n nie jest podane, to wycina wszystkie znaki od pozycji m do końca ciągu text. Jeżeli m jest ujemne, to znaki są odliczane od końca ciągu text.


Wyświetlenie pierwszych czterech znaków nazwiska.



Funkcja substr z użyciem dwóch parametrów – ostatnie litery nazwiska następujące po pierwszych trzech znakach.



Substr z m ujemnym. Parametr n najbezpieczniej w takiej sytuacji jest podać większy niż długość najdłuższego wyrazu.





Ltrim i Rtrim


RTRIM(text,text2)
LTRIM(text,text2)

Funkcje te usuwają z prawej lub lewej strony napisu text znaki zawarte w text2. Jeśli nie podamy wartości text2, usunięte zostaną spacje ( i w praktyce do tego tych funkcji używa się najczęściej).




W przypadku powyższym usunięte zostały litery występujące jako pierwsze od prawej jeśli były to A, T lub M


to_char


Funkcja to_char w Oracle ma kilka odmian. Może przyjmować różne parametry i służyć do różnych czynności. Poniższe zastosowanie funkcji to_char odnosi się do zamiany liczb na postać znakową. Inne formy to_char zostaną omówione w dalszych częściach tej publikacji.

to_char (liczba [,wzorzec]) - zamienia liczbę na postać znakową według podanego wzorca.


Gdybyśmy wyświetlili roczne wynagrodzenie pracowników podzielone przez 12 otrzymalibyśmy w niektórych rekordach mało czytelny wynik:



Stosując funkcję to_char możemy zamienić go na odpowiadający nam format:




Format jest napisem, który może zawierać następujące elementy:


Element Opis Przykład Wynik
9 Cyfra (liczba dziewiątek określa szerokość pola) 999999 1234
0 Wyświetl wiodące zera 099999 001234
$ Ruchomy znak dolara $999999 $1234
L Ruchomy znak lokalnej waluty L999999 zł1234
. Kropka dziesiętna na wskazanej pozycji 9999.99 1234.00
, Przecinek na wskazanej pozycji 999,999 1,234
MI Znak minus z prawej strony (przy wartościach ujemnych) 9999MI 1234-
PR Liczby ujemne w nawiasach 9999PR <1234>
EEEE Notacja inżynierska (w formacie muszą być cztery E) 9.9EEEE 1.2E+03
V Pomnóż przez 10 n razy (n to liczba dziewiątek po V) 9999V99 123400



to_number


to_number (text [,wzorzec]) - zamienia ciąg znaków na liczbę według wzorca zbudowanego podobnie jak powyżej.

Funkcja ta jest przydatna gdy w naszej bazie liczby przechowywane są w postaci tekstowej.



Length


length (text) - zwraca długość ciągu text, jeśli text ma wartość NULL funkcja zwraca NULL, a nie 0.


decode



Select decode (nazwa_kolumny,
wartość, zamiennik,
wartość2, zamiennik2,
wartość domyślna) [alias] from nazwa_tabeli.


Funkcja testuje wartość w podanej kolumnie i w zależności od wartości mieszczącej się w pierwszym argumencie zwraca wartość podaną w drugim argumencie. Jeśli nie znajdzie odpowiedniej wartości w podanych warunkach, wyświetli wartość domyślną podaną jaką ostatni warunek.



Ćwiczenia




1. Wyświetl imiona i nazwiska pracowników w taki sposób by wszystkie litery
w imionach były małe a w nazwiskach duże.
2. Wyświetl nazwiska pracowników w taki sposób by najpierw wszystkie je
zmniejszyć a następnie zwiększyć pierwszą literę.
3. Zamień wszystkie wystąpienia ciągu "Ma" w nazwiskach pracowników na "AM" przy pomocy replace
4. Zamień wszystkie literki e w imionach pracowników na Q przy pomocy
translate
5. Wyświetl litery od 3 do 6 z nazwisk pracowników
6. Wyświetl miesięczner zarobki pracowników z dokładnością do 2 cyfry po przecinku przy pomocy to_char
7. Wyświetl miesięczne zarobki pracowników z dokładnością do 2 cyfry po przecinku przy pomocy to_number
8. Wyświetl nazwiska pracowników oraz ich długość


Ten temat omawiam na poniższych szkoleniach:
Podstawy Oracle SQL
Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

10 komentarzy:

  1. Uwaga językowa: funkcja UPPER nie zwiększa liter, ale zamienia małe na wielkie. Zwiększyć można rozmiar czcionki, ale chyba aż tak zaawansowana funkcja UPPER nie jest ;-)

    OdpowiedzUsuń
  2. Hmm... wyrażenie:
    select to_char(salary/12,'99999.99') as Zarobki from employees;
    działa bardzo dobrze.
    Ma ktoś może pomysł dlaczego nie odpala:
    select to_number(salary/12,'99999.99') as "Zarobki miesieczne" from employees;
    ?

    OdpowiedzUsuń
    Odpowiedzi
    1. Dlatego że to_number zamienia tekst na liczbę. Ty usiłujesz zamienić liczbę na liczbę :) Przecież wynik salary/12 będzie liczbą :)

      Usuń
  3. w Lpad na końcu regułki powinno być z lewej strony ucina ,a nie z prawej :)

    OdpowiedzUsuń
  4. Funkcja substr z użyciem dwóch parametrów np: substr(last_name,3)
    następuje po 2 znakach

    select substr(last_name,3) , last_name from employees;

    OdpowiedzUsuń
  5. Moje propozycje rozwiązania zadań:

    1. Select lower(first_name) imiona, upper(last_name) nazwiska from employees
    2. Select initcap(lower(last_name)) nazwiska from employees
    3. Select replace(last_name, 'Ma', 'AM') nazwiska from employees
    4. Select translate(first_name, 'e', 'Q') imię from employees
    5. Select substr(last_name,3,3) skrot_nazwiska from employees
    6. Select to_char(salary/12, '99,999.99') placa_miesieczna from employees
    7. Select to_number(to_char(salary/12, '99,999.99'),'99,999.99') placa_miesieczna from employees
    8. Select last_name nazwiska, length(last_name) dlugosc_nazwiska from employees

    OdpowiedzUsuń
  6. Jaka jest różnica w działaniu funkcji replace i translate?

    OdpowiedzUsuń
    Odpowiedzi
    1. Róznica jest taka, że replace zamienia cały string na cały string, natomiast translate zastepuje pierwszy znak stringa w 2 parametrze funkcji znakiem z 3 parametru funkcji. Nastepnie bierze drugi znak stringa i zastepuje go 2 znakiem stringa z 3 parametru funkcji.

      Usuń
  7. Witam, Czy można w decode w miejsce wartość lub wartość2 wpisać przedział liczb??

    OdpowiedzUsuń
  8. Zabawne ale w REPLACE musiałem trzeci argument podać aby komenda mi poszła ale może to wina sql sandboxa na którym się bawię.

    OdpowiedzUsuń