Funkcje tekstowe mySQL – Kurs języka SQL

Funkcje tekstowe SQL wraz z przykładami

Poniżej przykładowa tabelka pracownik, która posłuży nam do niektórych przykładowych zapytań SQL:

id	imie	nazwisko	godzin	stawka	stranowisko
234	Jan	Kowalski	164	12.36	pracownik socjalny

Funkcje tekstowe SQL zostały podzielone w zależności od ich zastosowania.

Wielkość liter

LOWER() lub LCASE() – zamienia wszystkie litery w podanym ciągu (bądź kolumnie) na małe litery

SELECT LOWER('DAMIAN'); -- damian

SELECT LOWER(nazwisko) FROM pracownik; — nowak

UPPER() lub UCASE() – zamienia wszystkie litery w podanym ciągu (bądź kolumnie) na drukowane (wielkie litery)

SELECT UPPER('damian'); -- DAMIAN

SELECT UPPER(nazwisko) FROM pracownik; — NOWAK

Długość ciągu

BIT_LENGTH() – funkcja zwraca długość ciągu podaną w bitach

SELECT BIT_LENGTH('abcd'); -- 32

LENGTH() lub CHAR_LENGTH() lub CHARACTER_LENGTH() lub OCTET_LENGTH() – funkcja zwraca długość ciągu podanego jako argument. Najczęściej używa się funkcji LENGTH pozostałe realizują taką samą funkcje.

SELECT LENGTH('Damian'); -- 6

Puste znaki

TRIM() – usuwa puste znaki (spacje) na początku i końcu podanego ciągu

SELECT TRIM(' Przykładowy tekst '); -- "Przykładowy tekst"

LTRIM() – usuwa puste znaki (spacje) na początku podanego ciągu

SELECT LTRIM(' Przykładowy tekst '); -- "Przykładowy tekst "

RTRIM() – usuwa puste znaki (spacje) na końcu podanego ciągu

SELECT RTRIM(' Przykładowy tekst '); -- " Przykładowy tekst"

Łączenie ciągów

CONCAT() – łączenie ciągów, ciąg może być zawartością danej kolumny jak i dowolnym tekstem. Tekst wprowadza się wtedy w cudzysłowiach.

SELECT CONCAT(imie, nazwisko) FROM pracownik; -- JanKowalski

CONCAT_WS() – łączenie ciągów z zastosowaniem określonego separatora, np: ‘ ‘. Separator należy podać w cudzysłowiu.

SELECT CONCAT_WS(' ', imie, nazwisko) FROM pracownik; -- Jan Kowalski

INITCAP() – zamienia pierwszą literę w każdym słowie na dużą

SELECT INITCAP('pracownik socjalny'); -- Pracownik Socjalny

LPAD() – uzupełnienie ciągu z lewej strony o określony ciąg do określonej długości. W poniższym przypadku do ciągu nazwa będą dodawane “0” póki ciąg nie będzie miał długości 15 znaków.

SELECT LPAD('nazwa', '15', '0'); -- 0000000000nazwa

RPAD() – uzupełnienie ciągu z prawej strony o określony ciąg do określonej długości. W poniższym przypadku do ciągu nazwa będą dodawane “0” póki ciąg nie będzie miał długości 15 znaków.

SELECT RPAD('nazwa', '15', '0'); -- nazwa0000000000

SPACE() – funkcja wstawia określoną ilość spacji

SELECT SPACE(5); -- " "

SELECT concat(‘Piec’, space(5), ‘spacji’); — “Piec spacji”

INSERT() – funkcja wstawia do określonego ciągu zdefiniowany w funkcji ciąg począwszy od podanej pozycji zamieniając określoną ilość znaków. W poniższym przykładzie wstawiamy do ciągu “Matematyczny” ciąg “POLSKI” zaczynając od “3” znaku zastępując (kasując) “4” znaki ciągu Matematyka.

SELECT INSERT('Matematyczny', 3, 4, 'POLSKI'); -- MaPOLSKItyczny

REPEAT() – funkcja powtarza podany w parametrze ciąg określoną ilość razy, w poniższym przypadku ciąg “Wyraz” zostanie powtórzony “3” razy.

SELECT REPEAT('Wyraz', '3'); -- WyrazWyrazWyraz

Wycinanie ciągów i zastępowanie znaków/ciągów

REPLACE() – funkcja zamienia w podanym ciągu (kolumnie) wskazany ciąg znaków na inny określony ciąg znaków. W poniższym przykładzie dla podanego ciągu “nazwa” każde wystąpienie ciągu “az” zostanie zamienione na “X”.

SELECT REPLACE('nazwa', 'az', 'X'); -- nXwa

SELECT REPLACE(stanowisko,’socjalny’,’administracyjny’) FROM pracownik; — pracownik administracyjny

LEFT() – funkcja zwraca określoną ilość znaków licząc od lewej strony dla podanego ciągu. W naszym przykładzie zwróci 5 znaków od lewej.

SELECT LEFT('To jest zdanie normalne', '5'); -- To je

SELECT LEFT(stanowisko, 6) FROM pracownik; — pracow

RIGHT() – funkcja zwraca określoną ilość znaków licząc od prawej strony dla podanego ciągu. W naszym przykładzie zwróci 4 znaki od prawej.

SELECT RIGHT('To jest zdanie normalne', '4'); -- alne

SELECT RIGHT(stanowisko, 8) FROM pracownik; — socjalny

MID() – funkcja wycina z podanego ciągu określoną liczbę znaków rozopczynając od konkretnej pozycji. W poniższym przykładzie funkcja obetnie z ciągu “ToJestPrzykladoweZdanie” osiem znaków rozpoczynając od znaku siódmego.

SELECT MID('ToJestPrzykladoweZdanie', '7', '8'); -- Przyklad

SELECT MID(stanowisko,’6′,’4′) FROM pracownik; — wnik

SUBSTR() lub SUBSTRING() – funkcja wycina z podanego ciągu określoną liczbę znaków zaczynając od znaku określonego parametrem. W poniższym przykładzie z podanego ciągu “Nowy wyraz” po wycięciu 4 znaków licząc od 6 znaku powstanie nowy ciąg “wyra”.

SELECT SUBSTR('Nowy wyraz','6','4'); -- wyra

SELECT SUBSTR(stanowisko,’6′,’4′) FROM pracownik; — wnik

Funkcje porównujące

LIKE() – funkcja porównująca dwa argumenty, w przypadku gdy są takie same zwraca “1”, gdy różne zwraca “0”

SELECT 'A' like 'A'; -- 1

SELECT ‘A’ like ‘B’; — 0

NOT LIKE() – funkcja porównująca dwa argumenty, w przypadku gdy są takie same zwraca “0”, gdy różne zwraca “1” (negacja funkcji LIKE)

SELECT 'A' NOT like 'A'; -- 0

SELECT ‘A’ NOT like ‘B’; — 1

STRCMP() – funkcja porównuje dwa ciągi i zwraca: “0” – jeżeli oba ciągi są takie same, “-1” – gdy pierwszy ciąg jest mniejszy niż drugi, “1” – w pozostałych przypadkach

SELECT STRCMP('Wyraz','Wyraz'); -- 0

SELECT STRCMP(‘Wyraz’,’Inny wyraz’); — 1

REGEXP() lub RLIKE() – funkcja pozwalająca sprawdzić czy podany ciąg odpowiada zdefiniowanemu wzorcowi. W przypadku gdy porównanie da wynik pozytywny wtedy funkcja zwróci “1”, w przeciwnym przypadku “0”. W poniższym przykładzie sprawdzimy czy podany kod pocztowy pasuje do polskiego wzorca kodów pocztowych. Więcej informacji o funkcji REGEXP znajdziecie w osobnym artykule.

SELECT '60-300' REGEXP '[0-9][0-9]-[0-9][0-9][0-9]'; -- 1

SELECT ’60-3XA’ REGEXP ‘[0-9][0-9]-[0-9][0-9][0-9]’; — 0

NOT REGEXP() lub NOT RLIKE() – funkcja pozwalająca sprawdzić czy podany ciąg nie pasuje do zdefiniowanego wzorca. W przypadku gdy porównanie da wynik negatywny wtedy funkcja zwróci “1”, w przeciwnym przypadku “0”.

SELECT '60-300' NOT REGEXP '[0-9][0-9]-[0-9][0-9][0-9]'; -- 1

SELECT ’60-3XA’ NOT REGEXP ‘[0-9][0-9]-[0-9][0-9][0-9]’; — 0

SOUNDEX() – funkcja zwraca kod soundex dla podanego wyrazu. Funkcja używana do porównywania wyrazów, których wymowa jest identyczna mimo różnych zapisów. Wyrazy SEE oraz SEA mają różną pisownie mimo to brzmią tak samo, stąd oba otrzymują SOUNDEX równy S000.

SELECT soundex('home'); -- H500

SELECT SOUNDEX(‘see’), SOUNDEX(‘sea’); — S000 S000

SOUNDS LIKE() – funkcja zwraca rekordy dla których “warunek” brzmi jak zdefiniowany wzorzec. Dla poniższego przykładu załóżmy, że mamy tabelę wyrazy w której znajdują się wyrazy: sea, see, home, seek.
Funkcja wyświetli wyrazy, które wypowiedziane brzmią jak wyraz sea

SELECT wyraz FROM wyrazy WHERE wyraz SOUNDS LIKE 'sea'; -- see, sea

Funkcje wyszukujące

ELT() – funkcja zwraca wskazany argument podanego ciągu. W tym przypadku zwróci 3 argument, czyli ciąg: “Trzeci”

SELECT ELT(3, 'Pierwszy', 'Drugi', 'Trzeci'); -- Trzeci

FIELD() – funkcja zwraca pozycję wystąpienia danego ciągu w podanych ciągach. Zatem podany ciąg ‘Drugi’ zostanie znaleziony w zbiorze wartości ‘Pierwszy’, ‘Drugi’, ‘Trzeci’ na drugim miejscu

SELECT FIELD('Drugi', 'Pierwszy', 'Drugi', 'Trzeci'); -- 2

FIND_IN_SET() – funkcja zwraca pozycję wystąpienia danego ciągu w podanym ciągu

SELECT FIND_IN_SET('Drugi', 'Pierwszy,Drugi,Trzeci'); -- 2

INSTR() – funkcja wyszukuje w podanym ciągy określonego ciągu podając pozycję na której on występuje. W poniższym przykładzie ciąg “rz” rozpoczyna się na “8” pozycji ciągu “ToJestPrzykladoweZdanie”

SELECT INSTR('ToJestPrzykladoweZdanie', 'rz'); -- 8

LOCATE() lub POSITION() – funkcja zwraca miejsce wystąpienia określonego ciągu w danym ciągu. W poniższym przypadku ciąg “je” rozpoczyna się na 4 pozycji

SELECT LOCATE('je', 'To jest zdanie normalne'); -- 4

MAKE_SET() – funkcja zwraca ciąg określony “bitowo” przez pierwszy parametr, wyjaśnienie poniższego przykładu:

0 – 000 – wynik:
1 – 100 – wynik: pierwszy
2 – 010 – wynik: drugi
3 – 110 – wynik: pierwszy, drugi
4 – 001 – wynik: trzeci
5 – 101 – wynik: pierwszy, trzeci
6 – 011 – wynik: drugi, trzeci
7 – 111 – wynik: pierwszy, drugi, trzeci

SELECT MAKE_SET(5,'pierwszy','drugi','trzeci'); -- pierwszy,trzeci

EXPORT_SET() – funkcja zwracająca ciąg tekstowy według podanego wzorca. Dla poniższego przykładu będzie to ciąg składający się z “10” znaków rozdzielonych między sobą znakiem “|” gdzie na
czwartej pozycji wystąpi “X” zaś pozostałe znaki będą “o”. Wartość “8” podana jako parametr oznacza 4 bit.

SELECT EXPORT_SET(8,'X','o','|',10); -- o|o|o|X|o|o|o|o|o|o

Zatem patrząc “od tyłu” 2^0 2^1 2^2 2^3 – 0001 oznacza binarnie “8”. Inny przykład:

SELECT EXPORT_SET(17,'X','o','|',10); -- X|o|o|o|X|o|o|o|o|o

Zatem patrząc “od tyłu” 2^0*1 2^1*0 2^2*0 2^3*0 2^4*1 – 10001 oznacza binarnie 17″

Kodowanie i dekodowanie ciągów, konwersja/formatowanie ciągów i liter

FORMAT() – funkcja zwraca podaną wartość w określonym formacje, w poniższym przykładzie zaokrągloną do 4 miejsc po “kropce”. Warto zwrócić uwagę na separator tysięczny, którym jest znak “przecinka”

SELECT FORMAT(1234.123456, 4); -- 1,234.1235

QUOTE() – funkcja wstawiająca podany ciąg w cudzysłów

SELECT QUOTE('Teskt w cudzyslowiu'); -- 'Teskt w cudzyslowiu'

REVERSE() – funkcja odwraca “odbija” podany ciąg (kolejność liter)

SELECT REVERSE('To jest zdanie normalne'); -- enlamron einadz tsej oT

TO_BASE64() – kodowanie podanego tekstu przy wykorzystaniu algorytmu BASE64

SELECT TO_BASE64('To jest przykladowy tekst'); -- VG8gamVzdCBwcnp5a2xhZG93eSB0ZWtzdA==

FROM_BASE64() – odkodowanie podanego ciągu przy wykorzystaniu algorytmu BASE64

SELECT FROM_BASE64('VG8gamVzdCBwcnp5a2xhZG93eSB0ZWtzdA=='); -- To jest przykladowy tekst

CHAR() – podaje znak dla określonego kodu ASCII

SELECT CHAR(68); -- D

ASCII() lub ORD() – podaje kod ASCII dla określonego znaku (funkcja odwrotna do powyższej)

SELECT ASCII('D'); -- 68

BIN() – funkcja zwraca binarną wartość dla podanej liczby

SELECT BIN(17); -- 10001

OCT() – funkcja zwraca ósemkową wartość dla podanej liczby

SELECT OCT(11); -- 13

HEX() – zamienia podany ciąg/liczbę na postać heksadecymalną (szesnastkową)

SELECT HEX('Tekst'); -- 54656B7374

SELECT HEX(46548); — B5D4

UNHEX() – zamienia podaną wartość heksadecymalną (szesnastkową) na ciąg alfanumeryczny

SELECT UNHEX('54656B7374'); -- Tekst