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.

16 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ń
    Odpowiedzi
    1. z LEWEJ strony uzupełnia, jeżeli jest za krótki
      z PRAWEJ strony UCINA, jeżeli jest za długi.

      Usuń
  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ń
    Odpowiedzi
    1. Słusznie, powinno być od 3 znaku ... a nie po 3 znaku.

      Usuń
  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ń
    Odpowiedzi
    1. Ad. 5. substr(last_name,3,4) , bo ma wyświetlić od 3 litery do 6, więc literę z pozycji 3, 4, 5, 6. Daje nam to łącznie 4 litery.

      Usuń
  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ń
  9. Czy dla funkcji DECODE test może być bardziej złożony? Np. przedział, albo gdy różne od, itd. Czy zapytanie testuje tylko konkretną wartość?

    OdpowiedzUsuń
  10. Ten komentarz został usunięty przez autora.

    OdpowiedzUsuń
    Odpowiedzi
    1. przy czym po długości tekstu po 1. 2. 3. 4. mają często różne długości.
      Zawsze są rozdzielane znakiem |

      Usuń