średni SQLfunkcje tekstowePESELmatura rozszerzona z informatyki

Funkcje tekstowe i łączenie pól

Q: Czy w standardowym SQL-u pozycje też liczy się od 1?

Tak, to akurat jest zgodne z Accessem – SUBSTRING również zaczyna liczenie znaków od 1. Różni się tylko składnia wywołania i nazwy funkcji, nie sam sposób numerowania.

Wycinanie fragmentów tekstu funkcjami Left, Right, Mid i Len oraz sklejanie pól w jedną kolumnę – z przykładami na numerze PESEL.

Opis

Część informacji siedzi w środku wartości tekstowej, a nie w osobnej kolumnie. Numer PESEL koduje datę urodzenia na konkretnych pozycjach, kod pocztowy zawiera numer rejonu, sygnatura sprawy – rok. Żeby zapytać o taki fragment, trzeba go najpierw wyciąć. Służą do tego funkcje tekstowe.

Wszystkie działają na tej samej zasadzie: dostają tekst i informację, którą część z niego wziąć, a zwracają sam wycięty fragment. Można ich używać zarówno na liście kolumn po SELECT (żeby fragment pokazać), jak i w warunku WHERE (żeby po nim filtrować).

Numerowanie znaków zaczyna się od 1, nie od 0. To najczęstsze źródło pomyłek u osób przyzwyczajonych do programowania, gdzie pierwszy element ma zwykle numer zero. W SQL-u pierwszy znak tekstu to znak numer 1.

Wycinanie ze środka wymaga dwóch liczb: od którego znaku zacząć i ile znaków wziąć. Dzień urodzenia w numerze PESEL zajmuje pozycje piątą i szóstą, więc parametry to „zacznij od 5, weź 2 znaki".

Wynik wycięcia jest tekstem, nie liczbą. Porównując go, trzeba użyć apostrofów – '05', a nie 05. To ma znaczenie także przy sortowaniu: teksty porównują się znak po znaku, więc '10' jest „mniejsze" niż '9'. Jeśli potrzebna jest liczba, trzeba wycięty fragment jawnie przekonwertować.

Łączenie pól w jedną kolumnę. Często wygodniej pokazać „Kowalska Anna" w jednej kolumnie niż nazwisko i imię w dwóch. Do sklejania tekstów służy operator łączenia – w Accessie jest to znak &, w standardowym SQL-u podwójna kreska pionowa. Sklejać można zarówno kolumny, jak i wpisane wprost napisy: żeby między nazwiskiem a imieniem pojawiła się spacja, trzeba ją dokleić jako osobny tekst ' '.

Kolumnie powstałej ze sklejenia trzeba nadać nazwę etykietą AS – inaczej nagłówek będzie nieczytelny.

Do wyszukiwania po wzorcu, a nie po konkretnej pozycji, lepiej nadaje się operator LIKE.

Numerowanie znaków od 1. Pierwszy znak tekstu to znak numer jeden, nie zero.
Wynik wycięcia jest zawsze tekstem. Porównuj go w apostrofach; do obliczeń przekonwertuj jawnie na liczbę.
Sklejona kolumna potrzebuje aliasu. Bez etykiety AS nagłówek wyniku jest nieczytelny.
Nazwy funkcji i operator sklejania są dialektem Accessa. W standardowym SQL-u część z nich nazywa się i zapisuje inaczej – patrz sekcja Dialekt.

Funkcje

Left(tekst; ile)

znaki od lewejZwraca zadaną liczbę pierwszych znaków tekstu.

Right(tekst; ile)

znaki od prawejTo samo, licząc od końca.

Mid(tekst; od; ile)

fragment ze środkaZaczyna od znaku o podanym numerze i bierze zadaną ich liczbę.

Len(tekst)

długośćZwraca liczbę znaków. Przydatne do sprawdzania poprawności długości numeru.

UCase(tekst)

wielkie literyZamienia wszystkie litery na wielkie, reszty znaków nie rusza.

LCase(tekst)

małe literyZamienia wszystkie litery na małe.

InStr(tekst; szukany)

pozycja wzorcaZwraca numer znaku, od którego zaczyna się pierwsze wystąpienie szukanego fragmentu, albo 0, gdy go nie ma.

Trim(tekst)

obcięcie spacjiUsuwa spacje z początku i z końca tekstu; spacji w środku nie rusza.

Dialekt

W Accessie	Standardowy SQL	Znaczenie
`&`	`(podwójna kreska pionowa)`	łączenie tekstów w jedną wartość
`Mid(t; od; ile)`	`SUBSTRING(t FROM od FOR ile)`	wycięcie fragmentu ze środka
`Len(t)`	`LENGTH(t)`	długość tekstu w znakach
`UCase(t), LCase(t)`	`UPPER(t), LOWER(t)`	zamiana wielkości liter
`InStr(t; szukany)`	`POSITION(szukany IN t)`	pozycja wzorca w tekście – kolejność argumentów jest odwrócona

Zapytanie

-- Rocznik z numeru PESEL: dwie pierwsze cyfry, wynik jako tekst
SELECT nazwisko, pesel
FROM uczniowie
WHERE Left(pesel; 2) = '07'

Wynik zapytania

nazwisko	pesel
Kowalska	07231212345
Wiśniewski	07212812345
Dąbrowski	07283012345

-- Dzień urodzenia siedzi na pozycjach 5-6 - stąd Mid od 5 na długość 2
SELECT nazwisko, Mid(pesel; 5; 2) AS dzien
FROM uczniowie

Wynik zapytania

nazwisko	dzien
Kowalska	12
Nowak	05
Wiśniewski	28
Zając	19
Dąbrowski	30

-- Pierwsza litera nazwiska: Left z jednym znakiem
SELECT Left(nazwisko; 1) AS inicjal, imie
FROM uczniowie

Wynik zapytania

inicjal	imie
K	Anna
N	Piotr
W	Marek
Z	Julia
D	Karol

-- Sklejanie pól i wpisanego wprost tekstu w jedną kolumnę
SELECT nazwisko & ' ' & imie AS kto, klasa
FROM uczniowie

Wynik zapytania

kto	klasa
Kowalska Anna	3A
Nowak Piotr	3B
Wiśniewski Marek	3A
Zając Julia	3C
Dąbrowski Karol	3B

-- Surowy kod miesiąca z pozycji 3-4, bez korekty o 20 - patrz Protip niżej
SELECT nazwisko, Mid(pesel; 3; 2) AS kod_miesiaca
FROM uczniowie

Wynik zapytania

nazwisko	kod_miesiaca
Kowalska	23
Nowak	31
Wiśniewski	21
Zając	25
Dąbrowski	28

-- Len w warunku WHERE: tylko nazwiska dłuższe niż 8 znaków
SELECT nazwisko, Len(nazwisko) AS ile_znakow
FROM uczniowie
WHERE Len(nazwisko) > 8

Wynik zapytania

nazwisko	ile_znakow
Wiśniewski	10
Dąbrowski	9

-- Przypadek brzegowy: warunek na literze, której nie ma na początku żadnego nazwiska
SELECT UCase(nazwisko) AS wielkimi
FROM uczniowie
WHERE Left(nazwisko; 1) = 'X'

Wynik zapytania

wielkimi

Typowe błędy

Liczenie znaków od zera. Ktoś, kto pamięta indeksowanie z Pythona, próbuje wyciąć dzień urodzenia zapisem Mid(pesel; 4; 2). To przesuwa wycięcie o jedną pozycję w lewo i zamiast dnia daje ostatnią cyfrę miesiąca sklejoną z pierwszą dnia – wynik wygląda jak liczba, więc błąd łatwo przeoczyć. Poprawnie: Mid(pesel; 5; 2), bo dzień zaczyna się na piątej pozycji.

Porównanie wyciętego fragmentu z liczbą bez apostrofów. Zapytanie WHERE Left(pesel; 2) = 07 zamiast = '07' w Accessie najczęściej zgłasza błąd typu danych, bo 07 jako liczba to po prostu 7, a Left zwraca tekst. Efekt bywa też cichszy: pusta tabela wyników bez żadnego komunikatu. Wynik funkcji tekstowej trzeba porównywać z tekstem w apostrofach.

Zapomniana korekta +20 przy miesiącu. WHERE Mid(pesel; 3; 2) = '05' ma znaleźć osoby urodzone w maju, ale dla roczników 2000+ maj zapisany jest jako 25. Zapytanie nie zwróci błędu – po prostu cicho pominie właściwe osoby i pokaże pustą albo niepełną tabelę. Trzeba sprawdzić, z jakiego rocznika są dane, i doliczyć 20 tam, gdzie trzeba.

Brak aliasu dla sklejonej kolumny. SELECT nazwisko & ' ' & imie FROM uczniowie policzy wynik poprawnie, ale nagłówkiem kolumny będzie coś w rodzaju Expr1000 – nazwa nadana automatycznie przez silnik, bezużyteczna dla czytającego. Lekarstwo to AS kto po wyrażeniu.

Użycie + zamiast & do sklejania w Accessie. SELECT nazwisko + ' ' + imie FROM uczniowie bywa mylone ze sklejaniem, bo w niektórych bazach + też łączy teksty. W Accessie, jeśli którakolwiek z kolumn ma wartość NULL, cały wynik sklejenia przez + staje się NULL – znika cały wiersz z sensownego wyniku, a & w tej samej sytuacji po prostu pominie brakujący fragment.

Pomylone kierunki Left i Right. Zapytanie o rocznik czasem pisane jest jako Right(pesel; 2) zamiast Left(pesel; 2) – zamiast dwóch pierwszych cyfr (rok) wychodzą dwie ostatnie (fragment numeru porządkowego). Wynik jest liczbą podobnej wielkości, więc błąd nie rzuca się w oczy, dopóki ktoś nie porówna go z metryką ucznia.

Oczekiwanie, że Trim usunie spacje w środku tekstu. Trim(' Anna Maria ') da Anna Maria – podwójna spacja między imionami zostaje, bo Trim obcina tylko początek i koniec. Do spacji w środku potrzebna jest inna technika (zamiana fragmentu), nie Trim.

Zapamiętaj

Pierwszy znak tekstu ma numer 1, nie 0.
Left(tekst; ile) i Right(tekst; ile) biorą znaki od początku i od końca; Mid(tekst; od; ile) wycina ze środka.
Wynik każdej funkcji tekstowej to tekst – porównuj go w apostrofach.
Sklejanie w Accessie robi znak &; w standardowym SQL-u podwójna kreska pionowa.
Sklejonej kolumnie zawsze nadaj alias przez AS, inaczej nagłówek będzie nieczytelny.
Trim usuwa spacje tylko z początku i końca tekstu, nie w środku.
W kluczach CKE 2015–2025 nie ma zapytań z Left, Mid, Right ani Len – ćwicz je jednak, bo mechanika wraca w Excelu.

Sprawdź się

Fragment tabeli uczniowie (tylko dwie kolumny potrzebne do ćwiczenia):

nazwisko	pesel
Kowalska	07231212345
Nowak	06310512345
Wiśniewski	07212812345
Zając	06251912345
Dąbrowski	07283012345

Uzupełnij odpowiedzi na podstawie tabeli:

Pytanie	Odpowiedź
Jaką wartość zwróci `Left(pesel; 2)` dla Nowaka?
Jaką wartość zwróci `Mid(pesel; 5; 2)` dla Zając?
Ile wierszy zwróci `SELECT nazwisko FROM uczniowie WHERE Left(pesel; 2) = '06'`?
Jaki znak sklejenia tekstów działa w Accessie?
Jaką wartość zwróci `Len(nazwisko)` dla Wiśniewski?
Jaką wartość zwróci `UCase(nazwisko)` dla Zając?
Który operator porównuje wynik funkcji tekstowej jako tekst, a nie liczbę?
Ile wierszy zwróci zapytanie z warunkiem `Left(nazwisko; 1) = 'X'`?

Zapytanie SELECT nazwisko, Mid(pesel; 3; 2) AS kod FROM uczniowie WHERE nazwisko = zwróci dla tego ucznia kod miesiąca równy , co po odjęciu daje prawdziwy numer miesiąca .

Najczęściej zadawane pytania

Czy da się wyciąć fragment i od razu potraktować go jak liczbę?

Tak, ale wymaga to jawnej konwersji – w Accessie funkcją zamieniającą tekst na liczbę całkowitą. Bez niej porównania będą tekstowe, co przy liczbach o różnej liczbie cyfr daje mylące wyniki.

Co się stanie, gdy poproszę o więcej znaków, niż tekst ma?

Funkcja nie zgłosi błędu – zwróci po prostu tyle, ile jest. Wycięcie 5 znaków z trzyliterowego tekstu da ten trzyliterowy tekst.

Czy funkcje tekstowe działają na kolumnach liczbowych?

Zwykle tak, bo baza najpierw zamienia liczbę na tekst. Bywa to jednak zawodne przy liczbach ułamkowych – zależy od tego, jak zostaną wypisane. Jeśli numer ma być cięty na fragmenty, lepiej przechowywać go od początku jako tekst.

Czym różni się InStr od Left i Mid?

Left i Mid wycinają fragment tekstu na podstawie podanej pozycji. InStr niczego nie wycina – szuka wzorca i zwraca numer pozycji, na której się zaczyna, albo 0, gdy go nie znajdzie. Często łączy się je: najpierw InStr znajduje miejsce, potem Mid wycina fragment od tego miejsca.

Co zwróci Left albo Mid dla pustego tekstu?

Pusty tekst, bez błędu. Jeśli natomiast argumentem jest wartość NULL, wynikiem funkcji też jest NULL – funkcje tekstowe nie zamieniają braku wartości w pusty napis.

Dlaczego sortowanie po wyciętym fragmencie wygląda dziwnie dla liczb?

Bo wynik funkcji tekstowej to tekst, a teksty sortują się znak po znaku, nie jak liczby. '10' w takim sortowaniu wypada przed '9', bo znak '1' jest „mniejszy" od '9'. Żeby posortować numerycznie, trzeba jawnie skonwertować wycięty fragment na liczbę.

Czy w standardowym SQL-u pozycje też liczy się od 1?

Tak, to akurat jest zgodne z Accessem – SUBSTRING również zaczyna liczenie znaków od 1. Różni się tylko składnia wywołania i nazwy funkcji, nie sam sposób numerowania.

Przećwicz w zadaniach maturalnych

Do tego hasła nie mamy jeszcze przypisanych pojedynczych zadań. Cały spis zadań maturalnych z tego działu jest tutaj:

Zadania z działu: SQL

Komentarz Autora

Policz pozycje na kartce, zanim napiszesz zapytanie. Wypisz numer PESEL znak po znaku i ponumeruj je od 1. Pomyłka o jeden to najczęstszy błąd w tym temacie, a wynik wygląda wtedy zupełnie wiarygodnie – po prostu odpowiada na inne pytanie.

Uwaga na miesiąc w numerze PESEL. Dla osób urodzonych od 2000 roku do numeru miesiąca doliczane jest 20 – maj zapisany jest więc jako 25, a nie 05. Wycinanie pozycji 3–4 i porównywanie ich z numerem miesiąca „wprost" zadziała tylko dla roczników z XX wieku. Przy dzisiejszych maturzystach jest to gotowa pułapka, więc jeśli zadanie dotyczy miesiąca urodzenia, sprawdź najpierw, jakie roczniki są w danych.

Wynik wycięcia porównuj z tekstem w apostrofach. Warunek na wyciętym miesiącu to = '05', nigdy = 5. Zapis bez apostrofów albo zgłosi błąd, albo cicho zwróci pustą tabelę, bo '05' i 5 to dla bazy dwie różne rzeczy.

Sprawdź, jakim znakiem Twoje narzędzie skleja teksty. To kolejny punkt, w którym Access rozjeżdża się ze standardem. Jeśli sklejanie nie działa, to niemal na pewno przyczyna.

W kluczach odpowiedzi CKE z lat 2015–2025 nie pojawia się ani jedno zapytanie z funkcją Left, Mid, Right ani Len – to fakt z korpusu zadań, nie przeoczenie. Ćwiczenia na wycinaniu fragmentów tekstu (np. z numeru PESEL) CKE stawia w arkuszach z Excelem, nie z SQL-em – ta sama logika liczenia pozycji wraca tam w haśle Funkcje tekstowe.

To nie znaczy, że temat jest nieważny – wycinanie fragmentów pojawia się w podręcznikach i materiałach ćwiczeniowych jako naturalne rozszerzenie WHERE i SELECT, więc warto opanować mechanikę, zanim trafi się na nią w innej postaci. Traktuj tę stronę jako trening pozycji i indeksów, który przyda się także poza samym SQL-em.

Szymon

Autor materiałów na Prymo. Uczę informatyki do matury rozszerzonej – materiały powstają z tego, co realnie sprawia uczniom trudność na lekcjach.

↗

Powiązane zagadnienia

Zagadnienia z tego samego obszaru matury – warto je powtórzyć razem:

AS, wyrażenia i funkcja IIfSQL – funkcje i wyrażenia Funkcje datySQL – funkcje i wyrażenia AND, OR, NOT – łączenie warunkówSQL – zapytania BETWEEN i IN – skróty warunkówSQL – zapytania DISTINCT – usuwanie duplikatówSQL – zapytania Funkcje agregująceSQL – agregacja i grupowanie