Как соединить строки в sql
CONCAT returns char1 concatenated with char2 . Both char1 and char2 can be any of the data types CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , CLOB , or NCLOB . The string returned is in the same character set as char1 . Its data type depends on the data types of the arguments.
In concatenations of two different data types, Oracle Database returns the data type that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national data type, then the returned value is a national data type. For example:
CONCAT ( CLOB , NCLOB ) returns NCLOB
CONCAT ( NCLOB , NCHAR ) returns NCLOB
CONCAT ( NCLOB , CHAR ) returns NCLOB
CONCAT ( NCHAR , CLOB ) returns NCLOB
This function is equivalent to the concatenation operator (||).
Concatenation Operator for information on the CONCAT operator
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of CONCAT
Конкатенация в SQL-запросах
Конкатенацией называется операция, которая позволяет соединить несколько текстовых строк в одну.
В работе с базами данных конкатенация используется, когда нужно соединить значения нескольких полей или присоединить к значениям полей текст.
В SQL используется три вида конкатенации:
- Простая конкатенация;
- Конкатенация с разделителем;
- Групповая конктенация.
В различных «диалектах» языка SQL функции, реализующие конкатенацию, несколько различаются своим синтаксисом. Но принципы их работы одинаковы. Эти принципы будут рассмотрены на примере функций СУБД MySQL.
Простая конкатенация
Простая конкатенация в СУБД MySQL выполняется с помощью встроенной функции CONCAT() имеющей следующий синтаксис:
CONCAT(строка1, строка2, ……строкаN)
Строки, являющиеся аргументами функции, могут быть именами полей, а могут быть текстовыми строками в кавычках.
В качестве примера рассмотрим запрос:
SELECT concat(«Абитуриент «, fio, » возрастом «, age, » рекомендован(a) к зачислению») as text1 FROM abiturient;
Результатом запроса будет собранный из кусочков текст:
Конкатенация с разделителем
Этот вид конкатенации в MySQL выполняется с помощью функции CONCAT_WS(), имеющей следующий синтаксис:
CONCAT_WS(‘символ_разделитель’, строка1, строка2…)
В этом случае между соединяемыми строками будет установлен символ-разделитель.
Например, в данном запросе в качестве разделителя используется запятая:
SELECT concat_ws(‘,’, fio,age,gender) as text1 FROM abiturient;
Если в качестве разделителя использовать пустую строку, то результат будет полностью совпадать с результатом простой конкатенации. Если разделителем должен быть символ кавычки ‘, то его нужно экранировать обратным слешем – ‘\».
SELECT concat_ws(‘\», fio,age,gender) as text1 FROM abiturient;
Групповая конкатенация
Пусть даны две таблицы. В одной хранятся личные данные абитурентов, а в другой – поданные заявления. Считается, что каждый абитуриент может подать несколько заявлений на разные специальности. Тогда, чтобы увидеть список абитуриентов и список специальностей, на которые они подали заявления, нужно построить следующий запрос:
SELECT abiturient.idabiturient, abiturient.fio, application.namespec FROM abiturient JOIN application ON abiturient.idabiturient=application.idabiturient;
Результат запроса будет таким:
Теперь усложним задачу. Пусть необходимо собрать специальности по каждому абитуриенту в группу и записать их через запятую. То есть в результате должна получиться такая таблица:
Записанные через запятую названия специальностей, относящиеся к данному абитуриенту, — это и есть результат групповой конкатенации.
Для выполнения групповой конкатенации в MySQL используется инструкция GROUP BY и функция GROUP_CONCAT ().
GROUP_CONCAT([DISTINCT] строка1, строка2. [ORDER BY имя_поля или выражение [ASC | DESC]] [SEPARATOR ‘символ_разделитель’])
Необязательная инструкция DISTINCT позволяет удалить из списка совпадающие строки. Инструкция ORDER BY позволяет упорядочить строки по какому-либо полю или выражению. Упорядочивание по возрастанию определяется инструкцией ASC, по убыванию –DESC. Инструкция SEPARATOR позволяет задать символ, который будет разделять строки. По умолчанию разделителем является запятая.
Все запросы с групповой конкатенацией являются запросами группового типа. Функция GROUP_CONCAT() может быть отнесена к групповым операциям наряду с COUNT(), SUM(),MIN(),MAX(), AVG().
Таким образом, для получения списка специальностей по каждому абитуриенту нужно выполнить следующий запрос:
How to concatenate text from multiple rows into a single text string in SQL Server
Consider a database table holding names, with three rows:
Is there an easy way to turn this into a single string of Peter, Paul, Mary ?
47 Answers 47
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
Result I expected was:
I used the following T-SQL :
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don’t need to do a sub-query:
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Just some explanation (since this answer seems to get relatively regular views):
- Coalesce is really just a helpful cheat that accomplishes two things:
1) No need to initialize @Names with an empty string value.
2) No need to strip off an extra separator at the end.
- The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace ‘N/A’ with whatever is appropriate for you]).
SQL Server 2017+ and SQL Azure: STRING_AGG
Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.
Without grouping
With grouping:
With grouping and sub-sorting
One method not yet shown via the XML data() command in SQL Server is:
Assume a table called NameList with one column called FName,
Only the extra comma must be dealt with.
As adopted from @NReilingh’s comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:
In SQL Server 2005
In SQL Server 2016
And the result will become
This will work even your data contains invalid XML characters
You can replace ‘, ‘ with any string separator
And in SQL Server 2017, Azure SQL Database
In MySQL, there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
Then write the below code in SQL Server,
The output would be:
PostgreSQL arrays are awesome. Example:
Create some test data:
Aggregate them in an array:
Convert the array to a comma-delimited string:
Since PostgreSQL 9.0 it is even easier, quoting from deleted answer by "horse with no name":
Oracle 11g Release 2 supports the LISTAGG function. Documentation here.
Warning
Be careful implementing this function if there is possibility of the resulting string going over 4000 characters. It will throw an exception. If that’s the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters.
In SQL Server 2005 and later, use the query below to concatenate the rows.
A recursive CTE solution was suggested, but no code was provided. The code below is an example of a recursive CTE.
Note that although the results match the question, the data doesn’t quite match the given description, as I assume that you really want to be doing this on groups of rows, not all rows in the table. Changing it to match all rows in the table is left as an exercise for the reader.
I don’t have access to a SQL Server at home, so I’m guess at the syntax here, but it’s more or less:
In SQL Server 2017 or later versions, you can use the STRING_AGG() function to generate comma-separated values. Please have a look below at one example.
You need to create a variable that will hold your final result and select into it, like so.
Easiest Solution
In SQL Server vNext this will be built in with the STRING_AGG function. Read more about it in STRING_AGG (Transact-SQL).
A ready-to-use solution, with no extra commas:
An empty list will result in NULL value. Usually you will insert the list into a table column or program variable: adjust the 255 max length to your need.
(Diwakar and Jens Frandsen provided good answers, but need improvement.)
This worked for me (SQL Server 2016):
And a solution for MySQL (since this page show up in Google for MySQL):
Using XML helped me in getting rows separated with commas. For the extra comma we can use the replace function of SQL Server. Instead of adding a comma, use of the AS ‘data()’ will concatenate the rows with spaces, which later can be replaced with commas as the syntax written below.
With the other answers, the person reading the answer must be aware of a specific domain table such as vehicle or student. The table must be created and populated with data to test a solution.
Below is an example that uses SQL Server «Information_Schema.Columns» table. By using this solution, no tables need to be created or data added. This example creates a comma separated list of column names for all tables in the database.
If your data may get repeated, such as
Instead of having Tom,Ali,John,Ali,Tom,Mike
You can use DISTINCT to avoid duplicates and get Tom,Ali,John,Mike :
MySQL complete example:
We have users who can have much data and we want to have an output, where we can see all users’ data in a list:
Result:
Table Setup:
Query:
This puts the stray comma at the beginning.
However, if you need other columns, or to CSV a child table you need to wrap this in a scalar user defined field (UDF).
You can use XML path as a correlated subquery in the SELECT clause too (but I’d have to wait until I go back to work because Google doesn’t do work stuff at home 🙂
To avoid null values you can use CONCAT()
I really liked elegancy of Dana’s answer and just wanted to make it complete.
If you want to deal with nulls you can do it by adding a where clause or add another COALESCE around the first one.
This answer will require some privilege on the server to work.
Assemblies are a good option for you. There are a lot of sites that explain how to create it. The one I think is very well explained is this one.
If you want, I have already created the assembly, and it is possible to download the DLL file here.
Once you have downloaded it, you will need to run the following script in your SQL Server:
Observe that the path to assembly may be accessible to server. Since you have successfully done all the steps, you can use the function like:
Встроенные функции
Для работы со строка в 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 символов.