Лекція 8. Семестр 1. Функції в mySQL (Частина 2)

Строкові функції

         Для функцій, що працюють із позиціями в рядку, нумерація позицій починається з 1.
 

ORD(str)

Повертає код символів записаних в str, підтримуються багатобайтові символи.

SELECT ORD('2');       -> 50

CONV(N,from_base,to_base)

Перетворює числа з однієї системи числення в іншу. Повертає рядковий еквівалент числа N, перетвореного із системи числення з основою from_base у систему числення з основою to_base. Якщо хоча б один з аргументів рівний NULL, то вертається NULL. Аргумент N інтерпретується як ціле число, але може бути заданий як ціле число так і як  рядок. Мінімальне значення основи системи числення є 2, а максимальне – 36. 

SELECT CONV("a",16,2);       -> '1010'         
 

BIN(N)

Повертає рядок, що є двійковою величиною N, де N - ціле число великого розміру (BIGINT). Еквівалентна функції CONV(N,10,2). Якщо N дорівнює NULL, повертається NULL:

SELECT BIN(12);        -> '1100'

OCT(N)

Повертає рядковий еквівалент восьмеричного значення числа N, де N - ціле число великого розміру. Еквівалентно функції CONV(N,10,8). Якщо N дорівнює NULL, повертається NULL: 

SELECT OCT(12);       -> '14'

HEX(N_or_S)

Якщо N_OR_S - число, то повертається рядковий еквівалент шіснадцятрокового числа N, де N - ціле число великого розміру (BIGINT). Еквівалентна функції CONV(N,10,16). Якщо N_OR_S - рядок, то функція  повертає шіснадцятковий рядок N_OR_S, де кожний символ в N_OR_S конвертується в 2 шіснадцяткових числа.

SELECT HEX(255);     -> 'FF' 
SELECT HEX("abc");  -> 616263       
 

CHAR(N,...)

CHAR() інтерпретує аргументи як цілі числа й повертає рядок, що створюється із символів, що відповідають Ascii-Коду цих чисел. Величини NULL пропускаються:

SELECT CHAR(77,121,83,81,'76');     -> 'Mysql'        
 

CONCAT(str1,str2,...)

Повертає рядок, що є результатом конкатенації аргументів. Якщо хоча б один з аргументів рівний NULL, вертається NULL. Може обробляти більше 2 аргументів. Числовий аргумент перетвориться в еквівалентну строкову форму:

mysql> SELECT CONCAT('My', 'S', 'QL');     -> 'Mysql'        
 

CONCAT_WS(separator, str1, str2,...)

Функція CONCAT_WS() означає CONCAT With Separator (конкатенація з роздільником) і являє собою спеціальну форму функції CONCAT(). Перший аргумент є роздільником для інших аргументів. Роздільник, так само як і інші аргументи, може бути рядком. Якщо роздільник рівний NULL, то результат буде NULL. Дана функція буде пропускати всі величини NULL і порожні рядки, розташовані після аргументу-роздільника. Роздільник буде додаватися між рядками, що підлягають конкатенації:

SELECT CONCAT_WS(",","First name","Second name","Last Name");        

        -> 'First name,Second name,Last Name'

LENGTH(str)

OCTET_LENGTH(str)

CHAR_LENGTH(str)

CHARACTER_LENGTH(str)

Повертає довжину рядка str:

SELECT LENGTH('text');        -> 4     
SELECT OCTET_LENGTH('text');     -> 4

Зверніть увагу: для CHAR_LENGTH() і CHARACTER_LENGTH() багатобайтові символи враховуються тільки одного разу.

BIT_LENGTH(str)

Повертає довжину рядка str у бітах:

SELECT BIT_LENGTH('text');            -> 32

LOCATE(substr,str)

POSITION(substr IN str)

Повертає позицію першого входження підрядка substr у рядок str. Якщо підрядок substr у рядку str відсутній, вертається 0:

SELECT LOCATE('bar', 'foobarbar');  -> 4     

Дана функція підтримує багатобайтові символи. В Mysql 3.23 ця функція чутлива до регістру, а в 4.0 вона чутлива до регістру  тільки у випадку, якщо хоча б один з аргументів є рядком із двійковими даними.

LOCATE(substr,str,pos)

Повертає позицію першого входження підрядка substr у рядок str, починаючи з позиції pos. Якщо підрядок substr у рядку str відсутній, повертається 0: 

SELECT LOCATE('bar', 'foobarbar',5);           -> 7

Дана функція підтримує багатобайтові символи. В Mysql 3.23 ця функція чутлива до регістру, а в 4.0 вона чутлива до регістру,  тільки у випадку, якщо хоча б один з аргументів є рядком із двійковими даними.

INSTR(str,substr)

Повертає позицію першого входження підрядка substr у рядок str. Те ж, що й двоаргумантна форма функції LOCATE(), за винятком зміни місць аргументів:

SELECT INSTR('foobarbar', 'bar');      -> 4

Дана функція підтримує багатобайтові символи. В Mysql 3.23 ця функція чутлива до регістру, а в 4.0 вона чутлива до регістру  тільки у випадку, якщо хоча б один з аргументів є рядком із двійковими даними.

LPAD(str,len,padstr)

Повертає рядок str, який доповнюється ліворуч рядком padstr, поки рядок str не досягнеться довжини len символів. Якщо рядок str довше, чим len, то він буде вкорочений до len символів.

SELECT LPAD('hi',4,'??');       -> '??hi'

RPAD(str,len,padstr)

Повертає рядок str, який доповнюється праворуч рядком padstr, поки рядок str не досягнеться довжини len символів. Якщо рядок str довше, чим len, то він буде вкорочений до len символів.

SELECT RPAD('hi',5,'?');        -> 'hi???'

LEFT(str,len)

Повертає крайні ліворуч len символів з рядка str:

SELECT LEFT('foobarbar', 5); -> 'fooba'

Дана функція підтримує багатобайтові символи.

RIGHT(str,len)

Повертає крайні праворуч len символів з рядка str:

SELECT RIGHT('foobarbar', 4);          -> 'rbar'

Дана функція підтримує багатобайтові символи.

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

MID(str,pos,len)

Повертає підрядок довжиною len символів з рядка str, починаючи від позиції pos. Існує форма з оператором FROM, для якої використовується синтаксис ANSI SQL92:

SELECT SUBSTRING('Quadratically',5,6);     -> 'ratica'

Дана функція підтримує багатобайтові символи.

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

Повертає підрядок з рядка str, починаючи з позиції pos:

SELECT SUBSTRING('Quadratically',5);        -> 'ratically'

SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar'

Дана функція підтримує багатобайтові символи.

SUBSTRING_INDEX(str,delim,count)

Повертає підрядок з рядка str перед появою count входжень роздільника delim. Якщо count позитивний, то вертається все, що перебуває ліворуч від останнього роздільника (рухаючись ліворуч). Якщо count негативний, то вертається все, що перебуває праворуч від останнього роздільника (рухаючись праворуч):

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);       -> 'www.mysql'           

SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);      -> 'mysql.com'

Дана функція підтримує багатобайтові символи.

LTRIM(str)

Повертає рядок str з вилученими пробілами на початку:

SELECT LTRIM('    barbar');   -> 'barbar'

Дана функція підтримує багатобайтові символи.

RTRIM(str)

Повертає рядок str з вилученими пробілами в кінці:

SELECT RTRIM('barbar ');      -> 'barbar'

Дана функція підтримує багатобайтові символи.

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

Повертає рядок str з вилученими всіма префіксами й/або суфіксами, зазначеними в remstr. Якщо не зазначений жоден зі специфікаторів BOTH, LEADING або TRAILING, то мається на увазі BOTH. Якщо аргумент remstr не заданий, то видаляються пробіли:

SELECT TRIM('  bar  ');          -> 'bar'

SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');     -> 'barxxx'

SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');           -> 'bar'

SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');    -> 'barx'

Дана функція підтримує багатобайтові символи.

SPACE(N)

Повертає рядок, що створюється із N пробілів:

SELECT SPACE(6);-> '      '

REPLACE(str,from_str,to_str)

Повертає рядок str, у якому всі входження рядка from_str замінені на to_str:

SELECT REPLACE('www.mysql.com', 'w', 'Ww');      -> 'Wwwwww.mysql.com'

Дана функція підтримує багатобайтові символи.

REPEAT(str,count)

Повертає рядок, що створюється з рядка str, повтореного  count  разів. Якщо значення  count <= 0, повертає порожній рядок. Повертає NULL, якщо str або count рівні NULL:

SELECT REPEAT('Mysql', 3); -> 'Mysqlmysqlmysql'

REVERSE(str)

Повертає рядок str зі зворотним порядком символів:

SELECT REVERSE('abc');      -> 'cba'

Дана функція підтримує багатобайтові символи.

INSERT(str,pos,len,newstr)

Повертає рядок str, у якій підрядок починаючи з позиції pos, що має довжину len заміщена на newstr:

SELECT INSERT('Quadratic', 3, 4, 'What');    -> 'Quwhattic'

Дана функція підтримує многобайтные величини.

ELT(N,str1,str2,str3,...)

Повертає str1, якщо N = 1, str2, якщо N = 2, і так далі. Якщо N менше,
 ніж 1 або більше, ніж число аргументів, вертається NULL. Функція ELT() є доповняльною стосовно функції FIELD():

SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');            -> 'ej'

SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');            -> 'foo'

FIELD(str,str1,str2,str3,...)

Повертає індекс рядка str у списку str1, str2, str3, .... Якщо рядок str не знайдений, вертається 0. Функція FIELD() є додатковою стосовно функції ELT():

SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');          -> 2

SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');         -> 0

LCASE(str)

LOWER(str)

Повертає рядок str, у якому всі символи переведені в нижній регістр відповідно до поточної установки набору символів ( за замовчуванням - ISO-8859-1 Latin1):

SELECT LCASE('QUADRATICALLY');         -> 'quadratically'

Дана функція підтримує багатобайтові символи.

UCASE(str)

UPPER(str)

Повертає рядок str, у якому всі символи переведені у верхній регістр відповідно до поточної установки набору символів ( за замовчуванням - ISO-8859-1 Latin1):

SELECT UCASE('Hej');           -> 'HEJ'

Дана функція підтримує багатобайтові символи.

LOAD_FILE(file_name)

Читає заданий файл і повертає його вміст у вигляді рядка. Даний файл повинен перебувати на сервері, повинен бути зазначений повний шлях до цього файлу й користувач повинен мати привілей FILE. Розмір даного файлу повинен бути менше зазначеного в max_allowed_packet і файл повинен бути відкритий для читання для всіх. Якщо файл не існує або не може бути прочитаний по одній з вищезгаданих причин, то функція повертає NULL: 

UPDATE tbl_name     
       SET blob_column=LOAD_FILE("/tmp/picture")  
       WHERE id=1;

Функції порівняння рядків

       Звичайно якщо при виконанні порівняння рядків одне з виражень є залежним від регістру, то порівняння виконується також з урахуванням регістру.

expr LIKE pat

Функція робить порівняння із шаблоном, використовуючи операції порівняння простих регулярних виразів в SQL. Повертає 1 (ІСТИНА) або 0 (НЕПРАВДА). Вираз LIKE використомує два наступних символів-шаблонів в pat:
 

SELECT 'David!' LIKE '%D%v%';      -> 1

expr NOT LIKE pat

Те ж, що й NOT (expr LIKE pat).

STRCMP(expr1,expr2)

Функція STRCMP() повертає: 0, якщо рядки ідентичні, -1 - якщо перший аргумент менше другого ( відповідно до наявного порядку сортування), і 1 - в інших випадках:

SELECT STRCMP('text', 'text2');        -> -1

SELECT STRCMP('text2', 'text');        -> 1

SELECT STRCMP('text', 'text');                       -> 0

Функції, використовувані в операторах GROUP BY

Виклик групових функцій для Sql-Команд, що не містять GROUP BY, еквівалентний виконанню цих функцій над усім набором даних, що вертаються.

COUNT(expr)

Повертає кількість величин зі значенням, не рівним NULL, у рядках, отриманих за допомогою команди SELECT:

COUNT(DISTINCT expr,[expr...])

Повертає кількість величин, що недорівнює значенню expr.

AVG(expr)

Повертає середнє значення аргументу expr:

MIN(expr)

MAX(expr)

Повертає мінімальну або максимальну величину аргументу expr. Функції MIN() і MAX() можуть обробляти рядковий аргумент; у таких випадках вони повертають мінімальну або максимальну строкову величину.

SUM(expr)

Повертає суму величин в аргументу expr. Зверніть увагу: якщо набір даних, що повертається не містить ні одного рядка, то функція повертає NULL.

STD(expr)

STDDEV(expr)

Повертає середньоквадратичне відхилення значення в аргументі expr. Ця функція є розширенням ANSI SQL. Форма STDDEV() забезпечує сумісність із Oracle.