Как сделать конкатенацию в mysql
Добавил пользователь Евгений Кузнецов Обновлено: 04.10.2024
Как уже говорилось, в стандарте SQL определены только два типа функций упорядоченного набора: функции гипотетического набора (RANK, DENSE_RANK, PERCENT_RANK и CUME_DIST) и функции обратного распределения (PERCENTILE_DISC и PERCENTILE_CONT). Как я уже демонстрировал на примере функций сдвига, нет причины, по которой эта концепция не работала бы и для других функций. Основная идея состоит в том, что если это агрегирующая функция, результат вычислении которой зависит от порядка следования элементов, это возможный кандидат на функцию упорядоченного набора.
Возьмем такой классический пример как конкатенация строки. К сожалению, на настоящий момент не существует встроенной агрегирующей функции конкатенации строки, которая бы соединяла группу строк. Но допустим, что такая функция существует. Конечно же, у вас может возникнуть необходимость в конкатенации группы строк в некотором порядке, поэтому имеет смысл реализовать такую функцию как функцию упорядоченного набора с предложением WITHIN GROUP, которое позволяет задать параметры упорядочения.
В Oracle, например, такая функция реализована (называется LISTAGG) как функция упорядоченного набора. Итак, чтобы обратиться к таблице с именем Sales.Orders и вернуть для каждого клиента строку со значениями orderid конкатенированными в порядке orderid, используйте следующий код:
В SQL Server разработчики прибегают к самым разным альтернативным решениям, чтобы получить конкатенацию строк в определенном порядке. Один из наиболее эффективных приемов основывается на обработке XML с использованием параметра FOR XML в режиме PATH, примерно так:
Расположенный на самом нижнем уровне вложения связанный вложенный запрос отфильтровывает только значения orderid из таблицы Orders (псевдоним O), которые связаны с текущим клиентом из таблицы Customers (псевдоним C). Используя предложение FOR XML PATH, можно объединить все значения одну строку XML. Использование пустой строки как входного значения в режиме PATH означает, что инкапсулирующие элементы не нужны, поэтому мы получаем конкатенацию значений без всяких тегов. Так как вложенный запрос содержит ORDER BY orderid, значения orderid в строке будут упорядочены. Заметьте, что упорядочивать можно по любому признаку — не обязательно по значениям, которые конкатенируются. Приведенный код также добавляет запятую в качестве разделителя перед каждым значением orderid, а затем функция STUFF удаляет первую запятую. И наконец, функция COALESCE преобразует результат NULL в пустую строку. Итак, мы видим, что существует возможность получить в SQL Server конкатенацию строк в определенном порядке, но выглядит это не очень изящно.
Итак, функции упорядоченного набора, которые мы рассмотрели ранее, это агрегирующие функции, результат вычисления которых зависит от упорядочения. В стандарте определено несколько специализированных функций, но принцип является общим и может применяться ко всем видам вычислений агрегатов. Я привел несколько примеров, выходящих за пределы поддерживаемого стандарта, — это функции смещения и конкатенация строк. SQL Server 2012 не поддерживает функции упорядоченного набора данных, но я привел альтернативные методы для получения аналогичной функциональности. Я очень надеюсь, что в будущем мы увидим в SQL Server поддержку таких функций — возможно, они будут реализовывать стандартное предложение WITHIN GROUP и будут доступны через пользовательские CLR-функции агрегирования, учитывающие упорядочение.
Передо мной частенько возникают задачи: удалить часть текста из текстовых полей базы данных, объединить строковые данные или еще что-нибудь связанное с текстом. Делать все это через админские панели сайтов очень неудобно и муторно. Гораздо проще бывает написать запрос к базе данных выполняющий все эти действия за пару секунд.
Символьные функции в языке sql
Начнем по порядку с самого простого. Первой рассмотрим строковую функцию ASCII, которая используется для определения ASCII-кода текстовых символов:
integer ASCII(str string)
Функция возвращает целое значение — ASCII-код первого левого символа строки str. В случае если строка str пустая возвращает 0 и NULL если строка str не существует.
SELECT ASCII ('t');
Результат: 116
SELECT ASCII ('test');
Результат: 116
SELECT ASCII (1);
Результат: 49
Далее функция ORD, которая также определяет ASCII- код символов, но может обрабатывать также многобайтовые символы:
integer ORD(str string)
Если первый левый символ строки str многобайтовый, то возвращает его код в формате: ((первый байт ASCII- код)*256+(второй байт ASCII -код))[*256+третий байт ASCII -код. ]. В случае если первый левый символ строки str не является многобайтовым, работает как функция ASCII — возвращает его ASCII-код.
SELECT ORD ('test');
Результат: 116
Функция CHAR, тесно связанная с функцией ASCII и выполняет обратное действие:
string CHAR(int integer, . )
Функция CHAR возвращает строку символов по их ASCII-кодам. Если среди значений встречается значение NULL, то оно пропускается.
SELECT CHAR ( 116, '101', 115, '116' );
Результат: 'test'
SQL функции для объединения строк
Одна из самых популярных категорий функций. Ведь частенько бывает нужно объединить значения нескольких полей таблиц базы данных сайта. В языке SQL есть сразу несколько функций для конкатенации строк.
string CONCAT(str1 string, str2 string. )
Функция возвращает строку, созданную путем объединения аргументов. Можно указывать более двух аргументов. Если один из аргументов является NULL, то и возвращаемый результат будет NULL. Числовые значения преобразуются в строку.
SELECT CONCAT ('Hello', ' ', 'world', '!');
Результат: 'Hello world!'
SELECT CONCAT ('Hello', NULL, 'world', '!');
Результат: NULL
SELECT CONCAT ('Число пи', '=', 3.14);
Результат: 'Число пи=3.14'
Как видно из примеров, строки объединяются без разделителей. Для того чтобы разделить слова в первом примере в качестве аргумента приходится использовать пробел. Если бы слов было больше, то каждый раз вставлять пробелы было бы не очень удобно.
Для таких случаев существует функция CONCAT_WS:
string CONCAT_WS(separator string, str1 string, str2 string. )
Функция объединяет строки как и функция CONCAT, но вставляет между аргументами разделитель separator. В случае если аргумент separator является NULL, то и результат будет NULL. Аргументы строки равные NULL пропускаются.
SELECT CONCAT_WS (' ', 'Иванов', 'Иван', 'Иванович');
Результат: 'Иванов Иван Иванович'
SELECT CONCAT_WS (NULL, 'Иванов', 'Иван', 'Иванович');
Результат: NULL
SELECT CONCAT_WS (' ', 'Иванов', NULL, 'Иван', 'Иванович');
Результат: ''Иванов Иван Иванович'
В случае объединения большого количества строк, которые необходимо отделять разделителем, функция CONCAT_WS гораздо удобнее функции CONCAT.
Иногда бывает необходимо удлинить строку до определенного количества символов за счет повторения какого-либо символа. Это тоже своего рода объединение строк. Для этого можно использовать функции LPAD и RPAD. Функции имеют следующий синтаксис:
string LPAD(str string, len integer, padstr string)
string RPAD(str string, len integer, padstr string)
Функция LPAD возвращает строку str дополненную слева строкой padstr до длины len. Функция RPAD выполняет тоже самое, только удлинение происходит с правой стороны.
SELECT LPAD ('test', 10, '.');
Результат: . test
SELECT RPAD ('test', 10, '.');
Результат: test.
В данных функциях необходимо обратить внимание на параметр len, который ограничивает количество выводимых символов. Поэтому если длина строки str будет больше чем параметр len, то строка будет обрезана:
SELECT LPAD ('test', 3, '.');
Результат: tes
Определение длины строки в sql запросах
Для определения количества символов в строке в языке SQL отвечает функция LENGTH — длина строки:
integer LENGTH(str string)
Функция возвращает целое число равное количеству символов в строке str.
SELECT LENGTH ('test');
Результат: 4
В случае использования многобайтовых кодировок функция LENGTH выдает не правильный результат. Например в случае если задана кодировка unicode, то запрос:
SELECT LENGTH ('тест');
вернет 8. Что, легко заметить, в два раза больше реального количества символов. В этом случае нужно использовать функцию CHAR_LENGTH:
integer CHAR_LENGTH(str string)
Функция также возвращает длину строки str и поддерживает многобайтовые символы.
SELECT CHAR_LENGTH ('тест');
Результат: 4
Поиск подстроки в строке средствами sql
Для вычисления позиции подстроки в строке в языке sql существует несколько функций. Первая, которую мы рассмотрим, функция POSITION:
integer POSITION(substr string IN str string)
Возвращает номер позиции первого вхождения подстроки substr в строке str и возвращает 0 если подстрока не найдена. Функция POSITION может работать с многобайтовыми символами.
SELECT POSITION ('cd' IN 'abcdcde');
Результат: 3
SELECT POSITION ('xy' IN 'abcdcde');
Результат: 0
Следующая функция LOCATE позволяет начинать поиск подстроки с определенной позиции:
integer LOCATE(substr string, str string, pos integer)
Возвращает позицию первого вхождения подстроки substr в строке str, начиная с позиции pos. Если параметр pos не задан, то поиск осуществляется с начала строки. Если подстрока substr не найдена, то возвращает 0. Поддерживает многобайтовые символы.
SELECT LOCATE ('cd', 'abcdcdde', 5);
Результат: 5
SELECT LOCATE ('cd', 'abcdcdde');
Результат: 3
Аналогом функций POSITION и LOCATE является функция INSTR:
integer INSTR(str string, substr string)
Также как и функции выше возвращает позицию первого вхождения подстроки substr в строке str. Единственное отличие от функций POSITION и LOCATE то, что аргументы поменяны местами.
Далее рассмотрим функции, которые помогают получить подстроку.
Первыми рассмотрим сразу две функции LEFT и RIGHT, которые похожи по своему действию:
string LEFT(str string, len integer)
string RIGHT(str string, len integer)
Функция LEFT возвращает len первых символов из строки str, а функция RIGHT столько же последних. Поддерживают многобайтовые символы.
SELECT LEFT ('Москва', 3);
Результат: Мос
SELECT RIGHT ('Москва', 3);
Результат: ква
Далее рассмотрим одинаковые по итоговому результату функции SUBSTRING и MID:
string SUBSTRING(str string, pos integer, len integer)
string MID(str string, pos integer, len integer)
Функции позволяют получить подстроку строки str длиною len символов с позиции pos. В случае если параметр len не задан, то возвращается вся подстрока начиная с позиции pos.
SELECT SUBSTRING ('г. Москва — столица России', 4, 6);
Результат: Москва
SELECT SUBSTRING ('г. Москва — столица России', 4);
Результат: Москва — столица России
Примеры с функцией MID не привожу, потому что результаты будут аналогичные.
Интересная функция SUBSTRING_INDEX:
string SUBSTRING_INDEX(str string, delim string, count integer)
Функция возвращает подстроку строки str, полученную путем удаления символов, идущих после разделителя delim, находящимся в позиции count. Параметр count может быть как положительным, так отрицательным. Если count положительный, то отсчет позиции разделителя будет вестись слева и удаляться будут символы находящиеся справа от разделителя. Если count отрицательный, то отсчет позиции разделителя ведется справа и удаляются символы находящиеся слева от разделителя. Возможно, описание получилось слишком запутанным, но на примерах станет понятней.
Здесь функция ищет второе вхождение точки, удаляет все символы справа от нее и возвращает получившуюся подстроку. И еще один пример с отрицательным значением параметра count:
В этом примере функция SUBSTRING_INDEX ищет вторую точку, отсчитывая позицию справа, удаляет символы слева от нее и выдает полученную подстроку.
Удаление пробелов из строки
Для удаления лишних пробелов из начала и конца строки в языке SQL есть три функции.
string LTRIM(str string)
Удаляет с начала строки str пробелы и возвращает результат.
string RTRIM(str string)
Также удаляет пробелы из строки str, только с конца. Обе функции поддерживают многобайтовые символы.
SELECT LTRIM (' текст ');
Результат: 'текст '
SELECT RTRIM (' текст ');
Результат: ' текст'
И третья функция TRIM позволяет сразу удалять пробелы из начала и из конца строки:
string TRIM([[BOTH | LEADING | TRAILING] [remstr] string FROM] str string)
Параметр str обязательный, остальные параметры не обязательные. В случае если задан только один параметр str, то возвращает строку str удалив пробелы из начала и конца строки одновременно.
SELECT TRIM (' текст ');
Результат: 'текст'
С помощью пара метра remstr можно задавать символы или подстроки, которые будут удаляться из начала и конца строки. С помощью управляющих параметров BOTH, LEADING, TRAILING можно задавать откуда будут удаляться символы:
- BOTH — удаляет подстроку remstr с начала и с конца строки;
- LEADING — удаляет remstr с начала строки;
- TRAILING — удаляет remstr с конца строки.
SELECT TRIM (BOTH 'а' FROM 'текст');
Результат: 'текст'
SELECT TRIM (LEADING 'а' FROM 'текстааа');
Результат: 'текстааа'
SELECT TRIM (TRAILING 'а' FROM 'ааатекст');
Результат: 'ааатекст'
Функция SPACE позволяет получить строку состоящую из определенного количества пробелов:
string SPACE(n integer)
Возвращает строку, которая состоит из n пробелов.
Функция REPLACE нужна для замены заданных символов в строке:
string REPLACE(str string, from_str string, to_str string)
Функция заменяет в строке str все подстроки from_str на to_str и возвращает результат. Поддерживает многобайтные символы.
SELECT REPLACE ( 'замена подстроки', 'подстроки', 'текста' )
Результат: 'замена текста'
string REPEAT(str string, count integer)
Функция возвращает строку, которая состоит из count повторений строки str. Поддерживает многобайтовые символы.
SELECT REPEAT ('w', 3);
Результат: 'www'
Функция REVERSE переворачивает строку:
string REVERSE(str string)
Переставляет в строке str все символы с последнего на первый и возвращает результат. Поддерживает многобайтовые символы.
SELECT REVERSE ('текст');
Результат: 'тскет'
Функция INSERT для вставки подстроки в строку:
string INSERT(str string, pos integer, len integer, newstr string)
Возвращает строку полученную в результате вставки в строку str подстроки newstr с позиции pos. Параметр len указывает сколько символов будет удалено из строки str, начиная с позиции pos. Поддерживает многобайтовые символы.
SELECT INSERT ('text', 2, 5, 'MySQL');
Результат: 'tMySQL'
'SELECT INSERT ('text', 2, 0, 'MySQL');
Результат: 'tMySQLext'
SELECT INSERT ('вставка текста', 2, 7, 'MySQL');
Результат: 'SELECT INSERT ('вставка текста', 2, 7, 'MySQL');'
Если вдруг понадобиться заеменить в тексте все заглавные буквы на прописные, то можно воспользоваться одной из двух функций:
string LCASE(str string) и string LOWER(str string)
Обе функции заменяют в строке str заглавные буквы на прописные и возвращают результат. И та и другая поддерживают многобайтовые символы.
SELCET LOWER ('АБВГДеЖЗиКЛ');
Результат:'абвгдежзикл'
Если же наоборот необходимо прописные буквы заменить заглавными, то также можно применить одну из двух функцийй:
string UCASE(str string) и string UPPER (str string)
Функции возвращают строку str, заменив все прописные символы на заглавные. Также поддерживают многобайтовые символы.
Пример:
SELECT UPPER ('Абвгдежз');
Результат: 'АБВГДЕЖЗ'
Строковых функций в языке SQL немного больше, чем рассмотрено в данной статье. Но так как даже большинство рассмотренных здесь функций используются редко, я закончу их рассмотрение. В следующих статьях я постараюсь рассмотреть реальные практические примеры использования строковых функций SQL. Поэтому не забудьте подписаться на обновления блога. До новых встреч!
MySQL функция CONCAT позволяет объединять вместе два или более выражений.
Синтаксис
Синтаксис MySQL функции CONCAT:
Параметры или аргументы
expression1 , expressoin2 , . expression_n выражения для объединения.
Примечание
- Если expression является числовым значением, то оно будет преобразовано функцией CONCAT в двоичную строку.
- Если все expressions не являются двоичными строками, функция CONCAT вернет недвоичную строку.
- Если какое-либо из expressions является двоичной строкой, функция CONCAT вернет двоичную строку.
- Если какое-либо из выражений имеет значение NULL, функция CONCAT вернет значение NULL.
- См. Также функцию CONCAT_WS.
Применение
Функция CONCAT может использоваться в следующих версиях MySQL:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Пример
Рассмотрим примеры MySQL функции CONCAT, чтобы понять, как использовать функцию CONCAT в MySQL.
Например:
Для работы со строка в MySQL определен ряд встроенных функций:
CONCAT : объединяет строки. В качестве параметра принимает от 2-х и более строк, которые надо соединить:
При этом в функцию можно передавать не только непосредственно строки, но и числа, даты - они будут преобразовываться в строки и также объединяться.
CONCAT_WS : также объединяет строки, но в качестве первого параметра принимает разделитель, который будет соединять строки:
LENGTH : возвращает количество символов в строке. В качестве параметра в функцию передается строка, для которой надо найти длину:
LTRIM : удаляет начальные пробелы из строки. В качестве параметра принимает строку:
RTRIM : удаляет конечные пробелы из строки. В качестве параметра принимает строку:
TRIM : удаляет начальные и конечные пробелы из строки. В качестве параметра принимает строку:
С помощью дополнительного оператора можно задать где имеено удалить пробелы: BOTH (в начале и в конце), TRAILING (только в конце), LEADING (только в начале):
LOCATE(find, search [, start]) : возвращает позицию первого вхождения подстроки find в строку search. Дополнительный параметр start позволяет установить позицию в строке search, с которой начинается поиск подстроки find. Если подстрока search не найдена, то возвращается 0:
LEFT : вырезает с начала строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:
RIGHT : вырезает с конца строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:
SUBSTRING(str, start [, length]) : вырезает из строки str подстроку, начиная с позиции start. Третий необязательный параметр передает количество вырезаемых символов:
SUBSTRING_INDEX(str, delimiter, count) : вырезает из строки str подстроку. Параметр delimiter определяет разделитель внутри строки. А параметр count определяет, до какого вхождения разделителя надо вырезать подстроку. Если count положительный, то подстрока вырезается с начала, если count отрицательный, то с конца строки str:
REPLACE(search, find, replace) : заменяет в строке find подстроку search на подстроку replace. Первый параметр функции - строка, второй - подстрока, которую надо заменить, а третий - подстрока, на которую надо заменить:
INSERT(str, start, length, insert) : вставляет в строку str, заменяя length символов с позиции start подстрокой insert. Первый параметр функции - строка, второй - позиция, с которой надо заменить, третий - сколько символов с позиции start надо заменить вставляемой подстрокой, четвертый параметр - вставляемая подстрока:
REVERSE : переворачивает строку наоборот:
LOWER : переводит строку в нижний регистр:
UPPER : переводит строку в верхний регистр
SPACE : возвращает строку, которая содержит определенное количество пробелов
REPEATE(str, count) : возвращает строку, которая содержит определенное количество повторов подстроки str. Количество повторов задается через параметр count.
LPAD(str, length, pad) : добавляет слева от строки str некоторое количество символов, которые определены в параметре pad. Количество добавляемых символов вычисляется по формуле length - LENGTH(str) . Если параметр length меньше длины строки str, то эта строка усекается до length символов.
RPAD(str, length, pad) : добавляет справа от строки str некоторое количество символов, которые определены в параметре pad. Количество добавляемых символов вычисляется по формуле length - LENGTH(str) . Если параметр length меньше длины строки str, то эта строка усекается до length символов.
Читайте также: