Archiwum kategorii: SQL

PostgreSQL funkcja CONCAT() – łączenie stringów

Dzisiaj zaprezentuję Wam funkcję CONCAT() która łączy wiele stringów (łańcuchów znaków). Każdy kolejny string który chcemy złączyć jest kolejnym argumentem tej funkcji przedzielonym przecinkiem. Jeżeli będziemy chcieli dołączyć do naszego nowego łąńcucha znaków wartość NULL to będzie ona zignorowana. Ciekawą rzeczą która robi ta funkcja to niejawna kowersja np. typów liczbowych np. INT na typ znakowy, co zobaczycie na poniższym przykładzie.
Zwracany typ: TEXT

PostgreSQL CONCAT() składnia

CONCAT( argument1 , argument2 , … )
Pamiętaj tekst wsadzamy w pojedynczy cudzysłów 'text', liczbę piszemy normalnie.

 

Przykład zastosowania funkcji CONCAT()

SELECT
    CONCAT('Ala ma ' , 2 , ' koty ' , NULL , 'i ' , 1 , ' psa.')

Wynik

    Ala ma 2 koty i 1 psa.

zwróćcie uwagę, że mamy tutaj ciągu znaków np. 'Ala ma ', mamy także wartości liczbowe: 2 i 1 ale mam także wartość NULL. Ciągi znaków są normalnie dołączane do nawego wynikowego łańcucha znaków, wartości liczbowe są konwertowane na tekst i także są dołączane a wartość NULL jest pomijana.


postgresqlpostgresqlpostgresql

PostgreSQL funkcja CAST – konwertujemy łańcuch znaków na liczbę

Jedno z najczęście spotykanych pytań dotyczących konwersji. Jak zamienić string (łańcuch znaków) na liczbę. Sprawę załatwi nam funkcja CAST().
 

PostgreSQL CAST() składnia

CAST( wyrażenie AS docelowy_typ)

wyrażenie – tutaj definiujemy wyrażenie które chcemy przekonwertować do docelowego typu
docelowy_typ – tutaj określamy do jakiego typu chce przekonwertować nasze wyrażenie


Przykłady zastosowania funkcji CAST()

Przekonwertuj łańcuch znaków '100' na liczbę 100. Dla przetestowania czy konwersja się powiodła dodaj do przekonwertowanej liczy 100 wartość 300 i wyświetl wynik operacji.

SELECT
    CAST( '100' AS INT) + 300 AS Wynik

Wynik

400

Na początku przekonwertowaliśmy łańcuch znaków '100' na liczbę 100, czyli CAST('100' AS INT). Następnie dodaliśmy do liczby 100 liczbę 300. Wynik naszej operacji to 400.


postgresqlpostgresqlpostgresql

PostgreSQL funkcja LTRIM – wycinamy znaki z początku łańcucha głównego

Czasami przy wprowadzaniu danych do bazy zdaży się że na początku ciągu znaków wkradnie nam się niechciana spacja. Przy wyświetlaniu takich danych są to niepożądane znaki. I tutaj z pomocą przychodzi nam funkcja LTRIM(). Dzięki tej funkcji możemy wyciąć niechciane spacje z początku ciągu (lub inne zdefiniowane przez nas znaki). Zobaczmy jak to wygląda na przykładach.
Zwracany typ: TEXT

PostgreSQL LTRIM() składnia

LTRIM( łańcuch_główny , opcjonalnie_zdefiniowany_zbiór_znaków )

łańcuch_główny – tutaj określamy łańcuch główny z którego będziemy ucinać znaki.
opcjonalnie_zdefiniowany_zbiór_znakówto opcjonalny parametr gdzie deklarujemy zbiór znaków lub ciąg znaków który chcemy wyciąć od początku głównego łańcucha.


Przykłady zastosowania funkcji LTRIM()

SELECT
    LTRIM('Ala ma kota.' , 'lA')

Wynik

a ma kota.

Z lewej strony łańcucha głównego zostały wycięte dwa znaki 'Al'. Zwróć uwagę, że w parametrze zdefiniowaliśmy je w odwrotnej kolejności 'lA'. Widzimy więc, że dla funkcji LTRIM nie ma znaczenia kolejność znaków byleby wszystkie znaki z łańcucha głównego które chcemy wyciąć znalazły się w zdefiniowanych przez nas zbiorze znaków.

No to teraz zróbmy coś takiego.

SELECT
    LTRIM( 'Ala ma kota.' , 'la' )

Wynik

Ala ma kota.

Tutaj w wyniku nasz łańcuch główny nie został ruszony, dlaczego? Przecież zdefiniowaliśmy zbiór znaków 'la' więc LTRIM powinien uciąć coś z początku. Nasza funkcja nie ucięła niczego bo dla funkcji LTRIM wielkość znaków ma znaczenie. Masz łańcuch główny zaczyna się znakiem 'A' ale takiego znaku nie ma w zdefiniowanym przez nas zbiorze znaków. Jeżeli już pierwszy znak nie znajduje się w naszym zdefiniowanym zbiorze znaków, dalsze znaki z łańcucha głównego nawet nie są brane pod uwagę.

Kolejny przykład.

SELECT
    LTRIM('  Ala ma kota.')

Wynik

Ala ma kota.

Tutaj nie mamy zdefiniowanego zbioru znaków. W argumentach funkcji mamy tylko łańcuch główny. Ale zwróć uwagę, że na początku tego łańcucha znajdują się dwie spacje, które w wyniku zostały wycięte. Tak jak pisałem wcześniej jeżeli nie mamy drugiego argumentu w funkcji LTRIM, jedynie co funkcja sprawdzi i zrobi to czy na początku łańcucha głównego znajdują puste spacje i je usunie, tak jak to się wydarzyło w naszym przykładzie.

Teraz przerobimy bardzo fajny przykład.

SELECT
    LTRIM( '  Ala ma kota.' , 'Ala' )

Wynik

Ala ma kota.

Co się stało, przecież w drugim parametrze określiliśmy, że funkcja ma wyciąć ciąg 'Ala' a na początku łańcucha głównego jest słówko 'Ala'. Zwróć jednak uwagę, że na początku łańcucha głównego są dwie spacje, które także są znakami, więc dla funkcji LTRIM łańcuch główny zaczyna się od spacji a nie od znaku 'A', a my w naszym zdefiniowanym zbiorze znaków nie mamy uwzględnionej spacji dlatego funkcja nic nie wycięła.


postgresqlpostgresql

Baza Biblioteka 2. Pokaż 10 najczęściej wypożyczających czytelników.

SQL tutorial. Baza Biblioteka ćwiczenia. Środowisko SQL SERVER.

Baza: Biblioteka

Zadanie nr: 2

Treść:  Pokaż 10 najczęściej wypożyczających czytelników.

Polecenia/funkcje w zapytaniu: SELECT TOP, COUNT(), JOIN, GROUP BY, ORDER BY


SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


Rozwiązanie:

Zapytanie:

select top 10
     C.login
    ,COUNT(*) as [Liczba wypozyczen]

from
    Wypozyczenia W
    join Czytelnicy C on W.id_czytelnik = C.id_czytelnik
    
group by
    C.login    

order by
    COUNT(*) desc   

Wynik:

SQL tutorial.  SQL SERVER. Baza Biblioteka

Pobierz skrypt sql


 SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


 

Baza Biblioteka 1. Pokaż 10 najczęściej wypożyczanych książek.

SQL tutorial. Baza Biblioteka ćwiczenia. Środowisko SQL SERVER.

Baza: Biblioteka

Zadanie nr: 1

Treść:  Pokaż 10 najczęściej wypożyczanych książek.

Polecenia/funkcje w zapytaniu: SELECT TOP, COUNT(), JOIN, GROUP BY, ORDER BY


SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


Rozwiązanie:

Zapytanie:

select top 10
     K.tytul
    ,COUNT(*) as [Ilosc wypozyczen]

from
    wypozyczenia W
    join Ksiazki K on W.id_ksiazka = K.id_ksiazka
    
group by
     K.tytul
    ,W.id_ksiazka
    
order by
    COUNT(*) desc

Wynik:

SQL tutorial.  SQL SERVER. Baza Biblioteka

Pobierz skrypt sql


 SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka

PostgreSQL funkcja OVERLAY() – zamiana ciągów znaków

Ciekawą funkcją w PostgreSQL jest funkcja OVERLAY() dzięki której możemy podmienić jeden ciąg znaków na drugi.
Zwracany typ: TEXT

PostgreSQL OVERLAY() składnia

OVERLAY([łańcuch_główny] placing [łańcuch_do_podmiany] from [pozycja_od_której_zaczynamy_nadpisywanie] for [ile_znaków_nadpisujemy])

Przykład zastosowania funkcji OVERLAY()
Zamień w zdaniu 'Ala ma kota.' słowo 'kota' na 'psa'.

SELECT
    OVERLAY('Ala ma kota.' placing 'psa' from 8 for 4)

Wynik

Ala ma psa.

Zwóć uwagę, że zastąpiliśmy ciąg znaków składający się z czterech znaków, ciągiem znaków składającym się z trzech znaków. Funkcja działa jakby dwu etapowo. Pierwszy etap to wycięcie z łańcucha głównego wycinka który chcemy zastąpić, a drugi etap to wstawienie w to miejsce ciągu który chcemy do łańcucha głównego wstawić.

W pierwszym przykładzie w miejsce dłuższego wycinka wstawiliśmy krótszy ciąg. Spróbujmy teraz odwrotnie. Zamieńmy teraz słowo 'psa' na 'kota'.

SELECT
    OVERLAY('Ala ma psa.' placing 'kota' from 8 for 3)

Wynik

Ala ma kota.

Myślę, że już wszystko jasne.


postgresqlpostgresql

PostgreSQL funkcja TRIM (leading, trailing, both) – wycinamy znaki z łańcucha głównego

Kolejną przydatną funkcją dotyczącą łańcuchów znaków jest funkcja TRIM(). Dzięki tej funkcji możemy wycinać zdefiniowane znaki lub ciągi znaków z przodu, z tyłu lub z obu stron naraz z głównego łańcucha znaków. Pamiętaj przy funkcji TRIM() wielkość znaków ma znaczenie.
Zwracany typ: TEXT

PostgreSQL TRIM() składnia

TRIM( [słowo_kluczowe] [znaki] from łańcuch_główny )

słowo kluczowe – tutaj używamy słów kluczowych leading – oznacza cięcie z przodu ciągu, trailing – oznacza cięcie od tyłu ciągu, both – oznacza cięcie z obu stron naraz.
znaki – tutaj określamy ciąg znaków lub pojedyncze znaki do ucięcia.
łańcuch_główny – tutaj określamy łańcuch główny z którego będziemy ucinać znaki.


Przykłady zastosowania funkcji TRIM()

SELECT
    TRIM(leading 'ala' from 'Ala ma kota.')

Wynik

Ala ma kota

Wykorzystaliśmy słówko leading więc funkcja TRIM powinna wyciąć z łańcucha głównego z początku zdania wyraz 'ala'. Dlaczego tego nie zrobił? Jak wcześniej napisałem przy funkcji TRIM wielkość liter ma znaczenie. Zwróć uwagę, że 'ala' jako ciąg znaków do wycięcia  jest napisane od małej litery 'a' a 'Ala' w zdaniu głównym jest napisane od dużej litery 'A'.

No to teraz zróbmy to jak należy.

SELECT
    TRIM(leading 'Ala' from 'Ala ma kota.')

Wynik

 ma kota

Zwróć uwagę, że w wyniku ' ma kota' przed słówkiem 'ma' jest spacja. Pamiętaj, spacja to też znak, a kazaliśmy funkcji TRIM wyciąć tylko 'Ala'.

Teraz wytnijmy coś od tyłu ciągu

SELECT
    TRIM(trailing 'a.t' from 'Ala ma kota.')

Wynik

Ala ma ko

Tutaj mamy ciekawy przykład. Widzimy w wyniku, że ucieliśmy ciąg 'ta.' a w paramtrach określiliśmy ciąg 'a.t'. Jak już pisałm wcześniej kompilator sprawdza czy znaki na końcu łańcucha głównego odpowiadają znakom ze zdefiniowanego przez nas ciągu niezależnie od ich pozycji w ciągu.

Teraz utnijmy coś z obu stron.

SELECT
    TRIM(both 'atA' from 'Ala ma kota.')

Wynik

la ma kota.

Dlaczego z łańcucha głównego zostało wycięte tylko 'A' z początku ciągu? Bo w zdefiniowanych przez nas znakach znalazł się znak 'A' (nieważne na którym miejscu) a na końcu łańcucha głównego jest znak '.' którego nie było w zdefiniowanych przez nas znakach. Zasada jest taka że ucinanie znaków zaczyna się od wskazanego kierunku znak po znaku. Jeżeli w naszym ciągu znaków na początku lub na końcu będzie znak który nie znajduje się w zdefiniowanych przez nas znakach to ucinanie zostanie przerwane, nawet jeżeli zaraz za tym pierwszym znakiem są znaki które są zdefiniowane w naszym ciągu do ucięcia.

Jeżeli wyświetlamy lub chcemy obrabiać jakieś ciągi znaków i nie jesteśmy pewni czy dane nie zawierają zbędnych spacji na początku lub końcu łańcucha znaków (np. czy użytkownicy wprowadzający dane do bazy nie wstawili przez przypadek zbędnych spacji) to funkcja TRIM() nadaje się doskonale do tego zadania.

W naszym przykładzie użyjemy zdania 'Ala ma kota.' ale na początku i na końcu zdania wstawimy po dwie spacje. Wtedy nasz ciąg przybieże postać '  Ala ma kota.  '. Teraz funkcją TRIM() usuniemy zbędne znaki z początku i końca ciągu. Między słówkiem both a from nie wstawiamy żadnych znaków, bo chcemy żeby funkcja usunęła tylko zbędne spacje.

SELECT
    TRIM(both from '  Ala ma kota.  ')

Wynik

Ala ma kota.

W wyniku otrzymujemy zdanie 'Ala ma kota.' bez spacji na początku i na końcu zdania.

Funkcja TRIM() na jeszcze jedną składnię. Możemy napisać tak jak na początku

TRIM( [słowo_kluczowe] [znaki] FROM łańcuch_główny )

lub

TRIM( [słowo_kluczowe] FROM łańcuch_główny [znaki] )

efekt końcowy będzie identyczny


postgresqlpostgresql

PostgreSQL EXISTS – sprawdzamy czy zapytanie zwraca wyniki

Bardzo przydatną funkcją w PostgreSQL-u jest funkcja EXISTS. Za jej pomocą możemy sprawdzić czy zapytanie lub podzapytanie zwraca jakieś wyniki. Wielu początkujących programistów stawia znak równości między sytuacją, kiedy zapytanie wykonalo się poprawnie ale nie zwróciło żadnych wyników a sytuacją kiedy zapytanie wykonało się poprawnie i zwraca jakieś rekordy. W obu przypadkach zapytanie wykonało się bez błędów ale niekiedy nie zwraca żadnego wyniku (żadnego rekordu). Są takie sytuacje kiedy nasze dalsze działania uzależniamy od sytuacji gdy zapytanie zwraca jakieś rekordy. Ja także znalazłem się ostatnio w takiej sytuacji i moje poszukiwania zakończyły się sukcesem, funkcja EXISTS.
Zwracany typ: BOOLEAN (true or false)

PostgreSQL EXISTS() składnia

EXISTS(zapytanie)


Przykład zastosowania funkcji EXISTS() (Baza: Biblioteka)

Wyświetl wszystkich autorów ksiżek z tabeli 'Autorzy' jeżeli w tabeli tej znajduje się autor o imieniu i nazwisku: Jarosław Testowy

W tabeli 'Autorzy' znajduje się 1000 rekordów ale nie ma autora o takim imieniu i nazwisku, więc zapytanie nie powinno zwrócić żadnych wyników. Poniżej efekt uruchomienia zapytania.

postgresql funkcja function exists sprawdzamy czy zapytanie zwraca wyniki

Widzimy, że wszystko zadziałało poprawnie zapytanie nie zwróciło żadnych wyników bo wartość zwrócona przez funkcję EXISTS była false, z uwagi na to, że w bazie nie było żadnego autora o szukanym imieniu i nazwisku.

 


postgresqlpostgresql

T-SQL SQL Server / Funkcja CHARINDEX

Za pomocą funkcji CHARINDEX możemy sprawdzić czy dany łańcuch znaków zawiera jakiś poszukiwany przez nas ciąg znaków. Naszym poszukiwanym ciągiem będzie jakiś tekst. Funkcja ta zwróci nam pozycję pierwszego znaku poszukiwanego tekstu z pierwszego wystąpienia naszego poszukiwanego tekstu. Funkcja CHARINDEX() jest bardzo podobna w działaniu do funkcji PATINDEX(). Różnica między tymi funkcjami polega na tym, że zdefiniowany wzorzec (poszukiwany tekst) w funkcji CHARINDEX() nie może zawierać znaków wieloznacznych a wzorzec w funkcji PATINDEX() może zawierać takie znaki.

Przykład zastosowania funkcji CHARINDEX

Sprawdź czy w zdaniu 'Ala ma kota' znajduje się tekst 'kot' i zwróć pozycję od której zaczyna się ten tekst w zdaniu.

— Dekarujemy sobie łańcuch główny, czyli zdanie 'Ala ma kota'
DECLARE
    @zdanie NVARCHAR(11) = 'Ala ma kota'

SELECT
     CHARINDEX('kot' , @zdanie) as [Pozycja pierwszego wystąpienia ‚kot’]

wynik

Pozycja pierwszego wystąpienia 'kot'

8

dla funkcji CHARINDEX() wielkość znaków w tekście nie ma znaczenia poniżej przykład

DECLARE
    @zdanie NVARCHAR(11) = 'Ala ma kota'

SELECT
     CHARINDEX('KoT' , @zdanie) as [Pozycja pierwszego wystapienia ‚kot’]

Zwróć uwagę, że kot w zdaniu 'Ala ma kota' jest pisany małymi literami, a w funkcji CHARINDEX() chcemy znaleźć ciąg KoT, gdzie K i T pisane są dużymi literami.

wynik

Pozycja pierwszego wystąpienia 'kot'

8

Widzimy więc, że wielkość liter w szukanym wzorcu nie ma znaczenia.


  SQL tutorial. AdventureWorks exercises SQL SERVER

T-SQL SQL Server / Funkcja PATINDEX

Za pomocą funkcji PATINDEX możemy sprawdzić czy dany łańcuch znaków zawiera jakiś poszukiwany przez nas ciąg znaków. Naszym poszukiwanym ciągiem może być konkretny text lub wzorzec utworzony z symboli wieloznacznych np. wyrażony w postaci wyrażenia regularnego. Funkcja ta zwróci nam pozycję pierwszego znaku poszukiwanego tekstu (który może być także wzorcem) z pierwszego wystąpienia poszukiwanego ciągu znaków (lub ciągu który pasuje do zdefiniowanego wzorca). Funkcja PATINDEX() jest bardzo podobna w działaniu do funkcji CHARINDEX(). Różnica między tymi funkcjami polega na tym, że zdefiniowany wzorzec (poszukiwany tekst) w funkcji PATINDEX() może zawierać znakie wieloznaczne a w funkcji CHARINDEX() nie.

Przykład zastosowania funkcji PATINDEX

Sprawdź czy w ciągu znaków 'Pierwszy dokument ma numer PL1020304050, a drugi dokument ma postać PL6050403020' znajduje się podciąg znaków który jest zbudowany wg wzoru: dwie duże litery od A-Z + 10 cyfr i zwróć pozycję od której zaczyna się poszukiwany ciąg znaków w ciągu głownym oraz wytnij ten kawałek tekstu z łańcucha głównego.

— Dekarujemy sobie łańcuch główny
DECLARE
    @lancuch_glowny NVARCHAR(100) = 'Pierwszy dokument ma numer PL1020304050, a drugi dokument ma postać PL6050403020'

SELECT
     PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' , @lancuch_glowny) as [Pozycja pierwszego wystapienia lancucha]
    ,SUBSTRING(@lancuch_glowny , PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' , @lancuch_glowny),12) as [Skopiowany lancuch]


  SQL tutorial. AdventureWorks exercises SQL SERVER