Сообщить об ошибке.

MySQL: Функции для работы со строками

Строковые функции БД MySQL возвращают NULL, если длина результата больше, чем значение системной переменной max_allowed_packet (определяется в конфигурации сервера MySQL).

Для функций, которые работают с позициями строки, первая позиция имеет номер 1 (НЕ НОЛЬ).

Для функций, которые принимают аргументы длины, нецелочисленные аргументы округляются до ближайшего целого числа.

Содержание:


CHAR_LENGTH(str):

MySQL функция CHAR_LENGTH() возвращает длину строки str, которая измеряется в кодовых точках (количество символов). Многобайтовый символ считается одной кодовой точкой. Это означает, что для строки, содержащей два 2-байтовых символа, функция LENGTH() возвращает 12, тогда как CHAR_LENGTH() возвращает 6, как показано здесь:

mysql> SET @word:='Привет';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT LENGTH(@word), CHAR_LENGTH(@word);
        -> 12, 6

MySQL функция CHAR_LENGTH() возвращает NULL, если str имеет значение NULL.

CONCAT(str1, str2,...):

MySQL функция CONCAT() возвращает строку, полученную в результате объединения аргументов. Может иметь один или несколько аргументов. Если все аргументы являются обычными строками, то результатом будет обычная строка. Если аргументы включают какие-либо двоичные строки, то результатом будет двоичная строка. Числовой аргумент преобразуется в эквивалентную ему обычную строковую форму.

Функция CONCAT() возвращает NULL, если какой-либо аргумент равен NULL.

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

Для строк, заключенных в кавычки, конкатенация может быть выполнена путем размещения строк рядом друг с другом:

mysql> SELECT 'My' 'S' 'QL';
        -> 'MySQL'

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

MySQL функция CONCAT_WS() расшифровывается как "Concatenate With Separator" и является специальной формой CONCAT(). Первый аргумент separator является разделителем для остальных аргументов. Разделитель добавляется между строками, которые необходимо объединить. Разделителем separator может быть строкой, как и остальные аргументы. Если разделитель равен NULL, то результат равен NULL.

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

Функция CONCAT_WS() не пропускает пустые строки, но пропускает любые значения NULL после аргумента-разделителя.

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

MySQL функция ELT() возвращает N-й элемент списка строк: если N = 1, то str1, если N = 2 => str2, и так далее. Возвращает значение NULL, если N меньше 1, больше числа аргументов или равно NULL. Функция ELT() является дополнением к функции FIELD().

mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');
        -> 'Aa'
mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');
        -> 'Dd'

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

MySQL функция FIELD() возвращает индекс (позицию) аргумента str в списке str1, str2, str3, .... Возвращает 0, если аргумент str не найден.

Если все аргументы FIELD() являются строками, то все аргументы сравниваются как строки. Если все аргументы являются числами, они сравниваются как числа. В противном случае аргументы сравниваются как бинарные данные.

Если str равно NULL, то возвращаемое значение равно 0, поскольку NULL не позволяет сравнить равенство с любым значением. Функция FIELD() является дополнением к ELT().

mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
        -> 2
mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
        -> 0

FIND_IN_SET(str, strlist):

MySQL функция FIND_IN_SET() возвращает значение в диапазоне от 1 до N, если строка str находится в списке строк strlist, состоящем из N подстрок. Список строк strlist - это строка, состоящая из подстрок, разделенных символами. Если первый аргумент str представляет собой постоянную строку, а второй - столбец типа SET, то функция FIND_IN_SET() оптимизирована для использования битовой арифметики. Возвращает 0, если str отсутствует в strlist или если strlist является пустой строкой. Возвращает значение NULL, если любой из аргументов равен NULL. Эта функция не работает должным образом, если первый аргумент содержит символ запятой ','.

mysql> SELECT FIND_IN_SET('b', 'a,b,c,d');
        -> 2

FORMAT(X, D[,locale]):

MySQL функция FORMAT() форматирует число X в формате '#,###,###.##', округляет до D знаков после запятой и возвращает результат в виде строки. Если D равно 0, то результат не имеет десятичной точки или дробной части. Если X или D равно NULL, то функция возвращает NULL.

Необязательный третий параметр позволяет указать языковой стандарт, который будет использоваться для десятичной точки результирующего числа, разделителя тысяч и группировки между разделителями. Допустимые значения локали совпадают с допустимыми значениями системной переменной lc_time_names. Если языковой стандарт имеет значение NULL или не указан, то языковой стандарт по умолчанию - 'en_US'.

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1, 4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2, 0);
        -> '12,332'
mysql> SELECT FORMAT(12332.2,2, 'ru_RU');
        -> '12 332,20'

FROM_BASE64(str):

MySQL функция FROM_BASE64() принимает строку str, закодированную с помощью правил кодировки base-64, используемых TO_BASE64(), и возвращает декодированный результат в виде двоичной строки. Результат равен NULL, если аргумент равен NULL или не является допустимой строкой base-64. Подробности о правилах кодирования и декодирования см. в описании функции TO_BASE64().

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'

INSERT(str, pos, len, newstr):

MySQL функция INSERT() возвращает строку str, с подстрокой, начинающейся с позиции pos, и длиной символов len, замененной строкой new, возвращает исходную строку, если pos не находится в пределах длины строки. Заменяет остальную часть строки из позиции pos, если len не находится в пределах длины остальной части строки. Возвращает значение NULL, если какой-либо аргумент равен NULL.

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'

Эта функция является безопасной для многобайтовых строк.

INSTR(str, substr):

MySQL функция INSTR() возвращает позицию первого вхождения подстроки substr в строке str. Это то же самое, что и форма функция LOCATE() с двумя аргументами, за исключением того, что порядок аргументов обратный.

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0

Эта функция является многобайтовой и чувствительна к регистру, только если хотя бы один аргумент является двоичной строкой. Если любой из аргументов равен NULL, эта функция возвращает NULL.

LEFT(str,len):

MySQL функция LEFT() возвращает крайние левые символы, которые соответствуют количеству len из строки str или NULL, если какой-либо аргумент равен NULL.

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

Эта функция является безопасной для многобайтовых строк.

LENGTH(str):

MySQL функция LENGTH() возвращает длину строки str, измеренную в байтах. Многобайтовый символ считается как несколько байтов. Это означает, что для строки, содержащей шесть 2-байтовых символов, LENGTH() возвращает 12, тогда как функция CHAR_LENGTH() возвращает 6. Возвращает NULL, если str равен NULL.

mysql> SELECT LENGTH('привет');
        -> 12

Примечание. Пространственная функция Length() OpenGIS называется ST_Length().

LOAD_FILE(file_name):

MySQL функция LOAD_FILE() читает файл file_name и возвращает содержимое файла в виде строки. Для использования этой функции файл должен находиться на хосте сервера, необходимо указать полный путь к файлу и иметь привилегию FILE. Файл должен быть доступен для чтения сервером, а его размер должен быть меньше, чем max_allowed_packet байт (выставляется в конфигурации сервера MySQL). Если для системной переменной secure_file_priv задано непустое имя каталога, то загружаемый файл должен находиться в этом каталоге. (До MySQL 8.0.17 файл должен быть доступен для чтения всем, а не только серверу).

Если файл не существует или не может быть прочитан из-за невыполнения одного из предыдущих условий, то функция возвращает значение NULL.

Системная переменная character_set_filesystem управляет интерпретацией имен файлов, которые задаются в виде буквенных строк.

mysql> UPDATE table
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;

LOCATE(substr, str),
LOCATE(substr, str, pos),
POSITION(substr IN str):

Первый синтаксис MySQL функции LOCATE() возвращает позицию первого вхождения подстроки substr в строке str, второй синтаксис возвращает позицию первого вхождения подстроки substr в строке str, начиная с позиции pos. Возвращает 0, если substr отсутствует, Возвращает NULL, если какой-либо аргумент равен NULL.

Функция POSITION('substr' IN 'str') эквивалентна LOCATE('bar', 'foobarbar')

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT POSITION('bar' IN 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

Эта функция безопасна для двоичной строк и чувствительна к регистру только в том случае, если хотя бы один аргумент является двоичной строкой.

LOWER(str):

MySQL функция LOWER() возвращает строку str со всеми символами, преобразованными в нижний регистр в соответствии с текущим отображением набора символов, или NULL, если строка имеет значение NULL. Набор символов по умолчанию utf8mb4.

mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'

Функция LOWER()UPPER()) неэффективны при применении к двоичным строкам (BINARY, VARBINARY, BLOB). Чтобы выполнить преобразование двоичной строки в буквенный формат, сначала преобразуйте ее в простую строку, используя набор символов, соответствующий данным, хранящимся в строке:

mysql> SET @str = BINARY 'Привет Мир!';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
        -> 'Привет Мир!', 'привет мир!'

Для сопоставлений наборов символов Unicode LOWER() и UPPER() работают в соответствии с версией алгоритма сопоставления Unicode (UCA) в имени сопоставления, если оно есть, и UCA 4.0.0, если версия не указана. Например, utf8mb40900aici и utf8mb3unicode520ci работают в соответствии с UCA 9.0.0 и 5.2.0 соответственно, тогда как utf8unicodeci работает в соответствии с UCA 4.0.0. См. Раздел 10.10.1, «Наборы символов Unicode».

Эта функция является многобайтовой.

Функция LCASE(), используемая в представлениях, переписана как LOWER().

LPAD(str, len, padstr):

MySQL функция LPAD() возвращает строку str, дополненную слева строкой padstr до длины len символов. Если str длиннее, чем len, то возвращаемое значение сокращается до len символов.

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

Возвращает значение NULL, если какой-либо из его аргументов равен NULL.

LTRIM(str):

MySQL функция LTRIM() возвращает строку str с удаленными начальными пробелами. Возвращает NULL, если str имеет значение NULL.

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

Эта функция безопасна для многобайтовых строк.

QUOTE(str):

MySQL функция QUOTE() заключает строку в кавычки для получения результата, который может быть использован в качестве правильно экранированного значения данных в инструкции SQL. Возвращается строка, заключенная в одинарные кавычки, и каждому экземпляру обратной косой черты \, одинарной кавычки ' и ASCII NUL предшествует обратная косая черта. Если аргумент равен NULL, возвращаемое значение представляет собой слово NULL без заключения в одинарные кавычки.

mysql> SELECT QUOTE("Don't!");
--        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
--        -> NULL

REPEAT(str,count):

MySQL функция REPEAT() возвращает строку, состоящую из строки str, повторяющейся count количество раз. Если количество меньше 1, то возвращается пустая строка. Возвращает значение NULL, если значение str или count равно NULL.

mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'

REPLACE(str, from_str, to_str):

MySQL функция REPLACE() возвращает строку str со всеми вхождениями строки from_str, замененной строкой to_str. Функция замены строки подстрокой REPLACE() выполняет сопоставление с учетом регистра при поиске from_str.

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

Эта функция безопасна для многобайтовой строки. Она возвращает NULL, если любой из его аргументов равен NULL.

REVERSE(str):

MySQL функция REVERSE() возвращает строку str с обратным порядком символов или NULL, если str равно NULL.

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

Эта функция безопасна для многобайтовой строки.

RIGHT(str, len):

MySQL функция RIGHT() возвращает крайние правые символы len из строки str или NULL, если какой-либо аргумент равен NULL.

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

Эта функция безопасна для многобайтовой строки.

RPAD(str,len,padstr):

MySQL функция RPAD() возвращает строку str, дополненную справа строкой padstr длиной len символов. Если str длиннее len, возвращаемое значение сокращается до символов len. Если str, padstr или len равно NULL, то функция возвращает значение NULL.

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

Эта функция безопасна для многобайтовой строки.

RTRIM(str):

MySQL функция RTRIM() возвращает строку str с удаленными пробелами в конце.

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

Эта функция является многобайтовой и возвращает NULL, если str имеет значение NULL.

SPACE(N):

MySQL функция SPACE() возвращает строку, состоящую из N пробелов, или NULL, если N равно NULL.

mysql> SELECT SPACE(6);
        -> '      '

SUBSTRING(str, pos),
SUBSTRING(str FROM pos),
SUBSTRING(str, pos, len),
SUBSTRING(str FROM pos FOR len):

MySQL функция SUBSTRING() без аргумента len возвращает подстроку из строки str, начинающуюся с позиции pos. Если аргументом len присутствует, то возвращают подстроку длиной len символов из строки str, начиная с позиции pos. Синтаксис, который используют слова FROM, являются стандартным синтаксисом SQL. Также возможно использовать отрицательное значение для pos. В этом случае начало подстроки - это два символа от конца строки, а не от начала. Отрицательное значение может быть использовано для pos в любой из форм этой функции. Значение 0 для pos возвращает пустую строку.

Для всех форм синтаксиса SUBSTRING() позиция первого символа в строке, из которой должна быть извлечена подстрока, считается равной 1.

mysql> SELECT SUBSTRING('Quadratically', 5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically', 5, 6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

Эта функция является многобайтовой. Она возвращает NULL, если любой из его аргументов равен NULL.

Если len меньше 1, то результатом будет пустая строка.

SUBSTRING_INDEX(str,delim,count):

MySQL функция SUBSTRING_INDEX() возвращает подстроку из строки str перед подсчетом вхождений разделителя delim. Если значение count положительное, то возвращается все, что находится слева от конечного разделителя (отсчет слева). Если значение count отрицательно, то возвращается все, что находится справа от конечного разделителя (отсчет справа). SUBSTRING_INDEX() выполняет сопоставление с учетом регистра при поиске delim.

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

Функция SUBSTRING_INDEX() безопасна для многобайтовой строки. Возвращает NULL, если любой из ее аргументов равен NULL.

TO_BASE64(str):

MySQL функция TO_BASE64() преобразует строковый аргумент в кодированную форму base-64 и возвращает результат в виде символьной строки с набором символов соединения и сопоставлением. Если аргумент не является строкой, то он преобразуется в строку до того, как произойдет преобразование. Результат равен NULL, если аргумент равен NULL. Строки в кодировке Base-64 могут быть декодированы с помощью функции FROM_BASE64().

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'

Существуют различные схемы кодирования base-64. Это правила кодирования и декодирования, используемые TO_BASE64() и FROM_BASE64():

  • Кодировка для значения алфавита 62 - '+'.
  • Кодировка для значения алфавита 63 - '/'.
  • Закодированный вывод состоит из групп из 4 печатных символов. Каждые 3 байта входных данных кодируются с использованием 4 символов. Если последняя группа неполная, то она дополняется символами '=' до длины 4.
  • Новая строка добавляется после каждых 76 символов закодированного вывода, чтобы разделить длинный вывод на несколько строк.
  • Декодирование распознает и игнорирует новую строку, возврат каретки, табуляцию и пробел.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str):

MySQL функция TRIM() возвращает строку str со всеми удаленными префиксами или суффиксами remstr. Если ни один из спецификаторов BOTH, LEADING или TRAILING не задан, то предполагается, что это BOTH. Аргумент remstr является необязательным, и, если он не указан, то пробелы удаляются.

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

Эта функция является многобайтовой. Она возвращает NULL, если любой из его аргументов равен NULL.

UPPER(str):

MySQL функция UPPER() возвращает строку str, в которой все символы заменены на прописные в соответствии с текущим сопоставлением набора символов, или NULL, если str имеет значение NULL. Набор символов по умолчанию - utf8mb4.

mysql> SELECT UPPER('Привет Мир!');
        -> 'ПРИВЕТ МИР!'

Смотрите описание LOWER() для информации, которая также применима к UPPER(). Это информация о том, как выполняется преобразование регистра символов двоичных строк (BINARY, VARBINARY, BLOB), для которых эти функции неэффективны, и информацию о преобразовании регистра для наборов символов Unicode.

Эта функция является многобайтовой. Функция UCASE(), используемая в представлениях, переписана как UPPER().