Что делает функция SQL CONCAT? Concat sql описание

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

Функция uni() Поиск/замена символа по его юникодному номеру также может быть сделана при помощи функции uni().Пример функции uni(): Boouni(107,32)Designer найдет слово Book

Хэш-функция Однако описанная выше схема имеет ряд существенных недостатков. Она крайне медлительна и производит слишком большой объём данных - по меньшей мере вдвое больше объёма исходной информации. Улучшением такой схемы становится введение в процесс преобразования

Метод Concat Описание методовМетоды приведены для последовательности sequence of T. function Concat(second: sequence of T): sequence of T; Соединяет две последовательности, дописывая вторую в конец первой и возвращая результирующую

GROUP_CONCAT() в Mysql.
Сегодня хочу рассказать об интересной функции в Mysql.
Как оказалось, знают ее очень не многие программисты, уж не знаю почему ей мало кто пользуется, но я опросил десятка два своих знакомых и меньше половины знали о GROUP_CONCAT .
Эта функция работает примерно как CONCAT_WS (Объединяет строки с разделителем), только в отличии от CONCAT_WS способна объеденить результаты выборки из таблицы.

Синтаксис
GROUP_CONCAT( expr [,expr ...]
[,col_name ...]]
)

Приведу обычный пример с домашними животными
Например есть таблица, в которой каждому человеку присваивется жвивотное, которое у него есть.

CREATE TABLE `test` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(250) NOT NULL, # это имя человека
`pet` varchar(250) NOT NULL, # это домашнее животное
PRIMARY KEY (`id`)
);

Данные в табличке вот такие
маша - попугай
света - кошка
маша - хомяк
юля - собака
света - собака
маша - рыбки

Из этой таблички видно, что
у Маши есть попугай, хомяк и рыбки,
у Светы кошка и собака,
а у Юли только собака.
Так вот есть задача вывести всех людей из таблицы и чтобы возле каждого показывалось, какие животные у него есть.

Делаем вот такой запрос
SELECT `name`, GROUP_CONCAT(`pet`) as `pet`
FROM `test`
GROUP BY `name`

Результат получается такой
маша - попугай,хомяк,рыбки
света- кошка,собака
юля - собака

Получилось объединение всех животных каждого человека.
GROUP BY `name` в конце сгруппировывает одинаковые имена, можно сказать отбрасывает дубли.

Пример конечно не очень жизненый, не часто встречаются такие избыточные данные, обычно имена хранят в одной таблице, животных во второй, а связи в третьей таблице.
Вот как раз для такого случая и будет полезна GROUP_CONCAT() .
Приведу пример с вложенным запросом.

(select GROUP_CONCAT(`pet`) as `pet` from `test` where `name` = `name1`)as `pet`
FROM `test`

Этот пример использует вложенный select для создания поля со списком животных...
DISTINCT отбрасывает одинаковые имена. Поле name переименовываем, чтобы можно было во вложенном запросе правильно обратиться.
Этот пример уже более похож на пример из жизни.
Выводятся имена, отбрасывая одинаковые, выдергивается список животных для каждого имени.
Все очень просто. Не нужно получать каждое имя, а затем выдергивать животных для каждого имени, все происходит одним запросом.

А теперь подробнее про эту хитрую GROUP_CONCAT.

Разделитель.
По умолчанию используется разделитель запятая.
Если есть необходимость, можно задать другой разделитель

SELECT DISTINCT `name` as `name1`,
(select GROUP_CONCAT(`pet` SEPARATOR "::") as `pet` from `test` where `name` = `name1`)as `pet`
FROM `test`

Сейчас животные будут разделены не запятой, а двойным двоеточием (:: )

А остальные возможности этой функции понятны из описаного синтаксиса (сортировка и исключение одинаковых записей).

Несколько разочарований.

Ограничение 1024.
У этой функции есть ограничение на объем выводимых данных.
По умолчанию 1024 символа для каждого объединения - для каждой выводимой строки.
Если размер склееных данных больше, то он будет урезаться.
Чтобы расширить размер нужно выполнить команду SET group_concat_max_len =4096;
Если у вас есть привелегии, то вы расширите объем получаемых данных до 4096, можно и больше.
Но чаще всего на обычных хостингах таких привелегий нет.

Только текст.
Следущая особенность GROUP_CONCAT это работа только со строками.
Если вы захотите склеить числа, то у вас ничего не получится, нужно преобразовать число в текст.

Допустим вы хотите получить не животных, а список ID.
Обычный вариант работать не будет, нужно конвертировать число в текст, например вот так

SELECT DISTINCT `name` as `name1`,
(select CONVERT(GROUP_CONCAT(`id`) USING cp1251) from `test` where `name` = `name1`)as `pet_id`
FROM `test`

Вот такой вот обзор интересной функции GROUP_CONCAT().
Использовать ее лучше всего для информации, не стоит полагаться на то, что вы получите абсолютно все данные, так как данных может быть больше чем установлено в ограничении.
Например вы выводите на странице список товаров.
У каждого товара показывается цена, наличие, вес, краткое описание. И тут можно например для каждого товара показать еще в каких цветах имеется этот товар.
Так как на этой странице выводится много товаров, поэтому дополнительную информацию о товаре стоит показывать сжато, и вот тут и можно подсунуть информацию в каких цветах присутсвует товар. Вывести например 5 самых популярных цветов этого товара.
Совершенно очевидно, если выводятся 20 товаров на странице, то показывать для каждого товара информацию о цветах размером более 1024 символов вы никогда не будете, перегружать страницу нет смысла.
Вот в таких местах есть смысл использовать GROUP_CONCAT().

Комментарии

27.11.2008 Евгений
Не плохая заморочка

07.12.2008 Виола
Отличная возможность. Я знала о ней, но не знала, что там есть ограничение. Вот только непонятно, зачем преобразование в текст? Я получала как раз список ID и без преобразования.

07.12.2008 Вадим
Виола, наверное ваши ID хранились в текстовых полях.
Если поле в базе имеет числовой тип, то эта функция без преобразования не выведет ничего... Надо обязательно преобразовать в текст.

07.12.2008 Виола
Да нет, поле ID у меня было - int(11), автоинкрементное, первичный ключ. Возможно дело в том, что уже после выборки я делаю неявное преобразование в текст - цепляю результат к строке. Но в самой выборке ничего такого нет и она возвращает требуемый результат.

24.12.2008 Вадим
Ну тут сложно сказать в чем дело, возможно это связано как раз с вашими неявными преобразованиями...
Но я сам проверял на трех разных версиях mysql и конвертировать в текст обязательно нужно, иначе выдает не знаечение, а какую то информацию о поле.

24.02.2009 Сергио
Виола дело говорит, у меня тоже Int выводит нормально. Версия 5.1

24.04.2009 Гумер
СПАСИБО!!!

Очень понадобилось.

16.06.2009 Нигина
У меня ошибка, хотя все правилно скопировала;)

18.10.2009 tyman8992
mysql 5.0.51b, Таблица InnoDB - конвертирование INT(11) обязательно, только что проверил.

21.10.2009 Артур
Спасибо.
Получилось вывести результат в формате, аналогичном сводной таблице в Excel.

21.12.2009 Семён
По ходу есть еще одна особенность.
Результат объединения не может быть больше 255. Это печально:(

21.12.2009 админ
255 это совсем мало, может все таки 1024?

19.01.2010
mможно объеденять несколько полей и вставлять разделитель
например вполне работает
select msisdn, group_concat(week,"=",charging order by week) from y2009 group by msisdn
380xxxxxxxxxx 40=8,41=4,44=8,45=8,46=4,47=1,50=8,51=9,52=4,53=1

09.03.2010 Дмитрий
А у меня не получается вот запрос

SELECT product.id,title, catid, GROUP_CONCAT(`idSpecific`) AS `specific`
FROM `product`
LEFT JOIN `specification_items` ON product.id = idProduct
WHERE catid =46 GROUP BY title

Результат specific

09.03.2010 админ
эта функция работает только со строками.
а в вашем случае скорее всего идет обработка числа.
нужно конвертировать данные так
CONVERT(GROUP_CONCAT(`idSpecific`) USING cp1251)

24.03.2010 Дмитрий
Огромное спасибо переконвертировал:)

25.03.2010 AJ
а можно какнибудь избежать вложенных селектов? если в запросе нужно вывести несколько сотен (тысяч) записей, это же будет столько же вложенных запросов.

26.03.2010 виктор
а разве через JOIN не получается избежать вложенных селектов?
хотя join тут наверное не поможет, так как тут в одном запросе выбирается много строк...
наверное без вложенного селекта это не сделать.

24.05.2010 USvER
Хорошая функция! Помогла избежать изобретению велосипеда и собственной сериализации.

To AJ, А тут как не крути а подзапрос обязателен. Вопрос лиш в том кто будет выполнять этот подзапрос - клиент или сервер. Использовать термин "подзапрос" конечно не совсем коректно, я не уверен но скорее всего на сервере произойдет только одна выборка подзапроса сразу для всего внешнего запроса, что невозможно реализовать на клиенте.
Как жаль что есть ограничение. Оно вообще бессмысленно и обоснованно только как мера защиты отчего-то(Переполнение буфера?).

24.05.2010 USvER
Наверное неправильно выразился,
На сервере возможна оптимизация(в этом суть всех агрегатных функций).
Я точно не знаю насколько данная функция оптимизирована но она явно требует меньше ресурсов чем обработка данных в клиенте.

Работа с базами данных постоянно связана с получением результатов запросов. И в некоторых случаях эту информацию необходимо вывести на экран определённым образом или объединить с другими данными. Для решения этой проблемы существует функция SQL - CONCAT.

Что делает функция CONCAT?

При выполнении некоторых работ с возникает потребность в соединении строк с дополнительными данными или между собой. Для решения такой задачи существует функция SQL - CONCAT. При её использовании две или более строк будут объединены в одну. При этом операция конкатенации строк будет правильно произведена как при работе со строками, так и с численными переменными.

В некоторых базах данных SQL CONCAT имеет несколько разновидностей. Так, в MySQL она имеет свой аналог - CONCAT_WS. Различие между функциями несущественны: в случае работы первой, при объединении с аргументом, значение которого равно NULL, результатом конкатенации будет NULL, а при использовании второго варианта объединения нулевой операнд будет просто пропущен.

Синтаксис функции

При использовании функции CONCAT, SQL-синтаксис обязывает программиста использовать аргументы в том порядке, в каком операнды должны быть соединены. Сам синтаксис операции конкатенации строк довольно простой: после ключевого слова CONCAT в скобках через запятую указываются все необходимые аргументы или строки по порядку, а после закрывающейся скобки, в случае необходимости, указывается ключевое слово AS и имя переменной, куда будет записан полученный результат. Шаблон функции CONCAT выглядит следующим образом:

CONCAT (строка1, строка2 [, строка 3, ...]) .

Стоит отметить, что в качестве аргументов функции может использоваться как операнд численного и строкового значения, так и некоторая функция, возвращающая результат. Например, сама операция SQL CONCAT. Таким образом, операция поддерживает принцип вложенности.

Как обойтись без использования CONCAT?

В случае когда есть необходимость обойтись без использования операции, или программисту неизвестна функция CONCAT, SQL предлагает ещё один вариант осуществления конкатенации строк. Для этого необходимо воспользоваться оператором "+", однако такой подход имеет ряд особенностей, которые необходимо учитывать при работе с ним.

При использовании функции "+" в случае, если операндами являются числовые значения, результатом операции будет обыкновенное сложение чисел, а при необходимости объединить число со строкой без явного преобразования программа выдаст ошибку. Так, при выполнении следующего запроса результатом будет значение числа "6":

В остальном оба способа идентичны и возвращают одинаковый результат.

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

Функция CONCAT(str1, str2)

Данная функция выполняет конкатенацию строк str1 и str2. Если один из аргументов равен NULL, то он воспринимается как пустая строка. Если оба аргумента равны NULL, то функция возвращает NULL.

CONCAT("Test", NULL) x2,

CONCAT(NULL, "Test") x3,

CONCAT(NULL, NULL) x4

Для конкатенации строк Oracle поддерживает специальный оператор конкатенации «||» , который работает аналогично функции CONCAT, например:

SELECT CONCAT("У попа ", "была собака") x1,

"У попа " || "была собака" x2

На заметку

Не следует путать оператор конкатенации «||», эквивалентный вызову функции CONCAT, и оператор «+», применяемый в арифметических операциях. В Oracle это разные операторы, но за счет автоматического приведения типов возможны трудноуловимые ошибки, например:

SELECT "5" + "3" x1

В данном случае возвращается числовое значение 8, а не текстовая строка «53». Это связано с тем, что, обнаружив арифметическую операцию «+», Oracle автоматически пытается привести аргументы к типу NUMBER.

Функция LOWER(str)

Функция LOWER преобразует все символы строки str в строчные.

SELECT LOWER("TeXt DATA") X

Функция INITCAP(str)

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

SELECT INITCAP("ИваноВ петр сиДорович") X1

Функции LPAD(str,n, [,char]) и RPAD(str,n, [,char])

Функция LPAD возвращает строку str, дополненную слева символом char, до достижения строкой длины в n символов. По умолчанию символ-заполнитель равен пробелу и может не указываться. Если длина переданной функции строки больше n , то функция возвращает строку без изменений. Функция RPAD аналогична LPAD, но производит дополнение строки справа. Данные функции очень удобны для форматирования текстовой информации при подготовке отчетов.

SELECT LPAD("Test", 20) x1,

RPAD("Test", 20) x2,

LPAD("Test", 20, "_") x3

Функция REPLACE(str, search_str, [,replace_str])

Функция REPLACE осуществляет поиск образца search_str в строке str и каждое найденное вхождение заменяет на replace_str. По умолчанию replace_str равен пустой строке, поэтому вызов функции REPLACE с двумя аргументами приводит к удалению всех найденных вхождений. Поиск подстроки ведется с учетом регистра.

SELECT REPLACE("У попа была собака", "собака", "кошка") x1,

REPLACE("У попа была злая собака", "злая") x2,

REPLACE("У попа была собака", "Собака", "Кошка") x3

Функция TRANSLATE(str, from_mask, to_mask)

Функция TRANSLATE анализирует строку str и заменяет в ней все символы, встречающиеся в строке from_mask, на соответствующие символы из to_mask. Для корректной работы функции строки from_mask и to_mask должны иметь одинаковую длину или строка from_mask должна быть длиннее, чем to_mask. Если from_mask длинее, чем to_mask, и в процессе обработки строки str обнаружатся символы, соответствующие одному из символов from_mask, и при этом им не найдется соответствия в to_mask, то такие символы будут удалены из строки str. Если передать from_mask или to_mask, равное NULL, то функция возвратит значение NULL. Сравнение производится с учетом регистра.

SELECT TRANSLATE("Test 12345", "e2", "E!") x1,

TRANSLATE("Test 12345", "e234", "E") x2

Данная функция удобна для решения ряда практических задач, связанных с перекодировкой символов или с поиском запрещенных символов. Например, необходимо проанализировать пароль и выяснить, содержит ли он хотя бы одну цифру. Реализация данной проверки при помощи TRANSLATE имеет вид:

IF TRANSLATE(PassWd, "0123456789", "*") = PassWd THEN

ADD_ERROR("Ошибка - Пароль должен содержать хотя бы одну цифру!");

Другой пример: идет подготовка числа к его преобразованию в NUMBER. Необходимо заменить разделители десятичных знаков «,» и «.» на «.» и удалить пробелы. Реализация данной операции при помощи TRANSLATE имеет вид:

SELECT TRANSLATE("123 455,23", "., ", "..") x1,

TRANSLATE("-123 455.23", "., ", "..") x2

Функция SUBSTR(str, m [,n])

Функция SUBSTR возвращает фрагмент строки str, начиная с символа m длиной n символов. Длину можно не указывать - в этом случае возвращается строка от символа m и до конца строки str. Нумерация символов идет с 1. Если указать m = 0, то копирование все равно начнется с первого символа. Задание отрицательного значения m приводит к тому, что символы отсчитываются от конца строки, а не от начала. Задание значений m , превышающих по абсолютному значению длину строки, приводит к тому, что функция возвращает NULL.

SELECT SUBSTR("У попа была собака", 13) x1,

SUBSTR("У попа была собака", -6) x2,

SUBSTR("Это тестовый текст", 5, 8) x3,

SUBSTR("У попа была собака", 150) x4

Функция INSTR(str, search_str [,n[,m]])

Функция INSTR возвращает позицию первого символа m -го фрагмента строки str, совпадающего со строкой search_str. Сравнение ведется с n-го символа строки str, при сравнении учитывается регистр. По умолчанию n = m = 1, то есть поиск ведется от начала строки и возвращается позиция первого найденного фрагмента. В случае неуспешного поиска функция возвращает 0.

SELECT INSTR("У попа была собака", "собака") x1,

INSTR("У попа была собака", "кошка") x2,

INSTR("Это текст для демонстрации поиска текста", "текст", 1, 2) x3,

INSTR("11111000000001", "1", 7) x4

На заметку

С данной функций, равно как и со всеми остальными в Oracle, часто допускаются типовые ошибки, связанные с обработкой значения NULL. Если str=NULL, то функция вернет NULL, а не ноль! Это необходимо учитывать при построении различных условий. Например, данный фрагмент программы на PL/SQL как раз не учитывает эту особенность:

IF INSTR(TXT_VAR, "*") = 0 THEN

В данном случае правильно было бы написать так:

IF NVL(INSTR(TXT_VAR, "*"), 0) = 0 THEN

Функции LENGTH(str) и LENGTHB(str)

Функция LENGTH(str) возвращает длину строки str в символах. Для пустой строки и значения NULL функция возвращает NULL, поэтому совместно с данной функцией рекомендуется использовать NVL.

SELECT LENGTH("У попа была собака") x1,

LENGTH(NULL) x3,

NVL(LENGTH(""), 0) x4

Функция CHR(n)

Возвращает символ по его коду.

SELECT CHR(64) x1

Функция Concat (Str1,Str2,...,StrN) выполняет конкатенацию (или сцепление) строк Str1,Str2,...,StrN в том порядке, в каком они указаны в списке параметров. Сумма символов всех сцепленных строк не должна превышать 255.

Program DemoFunctionConcat; Var Word: string; Word1, Word2: string; Begin Word1:= "фирмы "; Word2:= "Microsoft"; Word:= Concat("Компьютеры ",Word1,Word2); writeln(Word); {выводится текст "Компьютеры фирмы Microsoft"} End.

Задачи для самостоятельного решения

Задано существительное 1-го склонения, оканчивающееся на “а”. Проверьте правильность ввода. Напечатайте это слово во всех падежах. Применяйте подпрограммы.

Проверьте правописание “ча” и “ща” в тексте. Выведите на экран количество сделанных ошибок и исправленную строку. Применяйте подпрограммы.

Напишите алгоритм, подсчитывающий, сколько раз в данном слове х встречается данное слово у. Если слово у длиннее, чем х, то результат должен быть равен нулю. Применяйте подпрограммы.

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

Запишите строку а в обратном порядке в строку б. Посчитайте, сколько одинаковых букв находятся на одинаковых местах в этих строках. Применяйте подпрограммы.

Заданы фамилия, имя и отчество учащегося, разделенные пробелом. Напечатайте его фамилию и инициалы. Применяйте подпрограммы.

Проверьте правописание “жи” и “ши” в тексте. Выведите на экран количество сделанных ошибок и исправленную строку. Применяйте подпрограммы.

Запросите произвольное предложение и имя. Найдите ошибки употребления имени и исправьте их. Выведите на экран количество сделанных ошибок и исправленную строку. Применяйте подпрограммы.

Посчитайте количество цифр в веденной строке символов. Применяйте подпрограммы.

Посчитайте количество букв в введенной строке символов. Применяйте подпрограммы.

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

Стандартные процедуры для работы со строками (delete, insert,str,val).

Процедура Insert

Процедура Insert вставляет в исходную строку, начиная с указанной позиции, какую-либо другую строку. Оператор Insert (Word1,Word2,5) указывает, строку Word1 необходимо вставить в строку Word2, начиная с 5-ой позиции.

Процедура Delete

Процедура Delete удаляет в исходной строке фрагмент определенной длины, начиная с указанной позиции. Так, оператор Delete(Word1,2,3) удаляет из указанной строки фрагмент, длиной в три символа, начиная со второго.

Процедура Str

Общий вид Str(Chislo,Stroka)

Процедура Str преобразовывает числовое значение переменной Chislo в строковую переменную Stroka. После первого параметра может указываться формат, аналогичный формату вывода.

Program DemoProcedureStr; Var Word: string; Chislo: integer; Begin Chislo:= 1560; Str(Chislo:8, Word); writeln(Word); {выводится строка " 1500"} End.

Процедура Val

Общий вид Val(Stroka,Chislo,Code)

Процедура Val преобразует значение строки Stroka в величину целочисленного или вещественного типа и помещает результат в Chislo. Значение строковой переменной Stroka не должно содержать пробелов в начале и в конце. Code целочисленная переменная. Если во время операции преобразования ошибки не обнаружено, значение Code равно нулю, если же ошибка обнаружена, Code будет содержать номер позиции первого ошибочного символа, а значение Chislo будет не определено.

Program DemoProcedureVal; Var Word: string; Chislo, Code: integer; Begin writeln("Введите строку цифр "); readln(Word); Val(Word, Chislo, Code); {преобразование строки в число} if Code <> 0 then writeln("Ошибка! В позиции ",Code," не цифра!",); End.