MySQL CONCAT Function
Summary: in this tutorial, you will learn various ways to concatenate two or more strings together by using the MySQL CONCAT and CONCAT_WS functions.
To concatenate two or more quoted string values, you place the string next to each other as the following syntax:
MySQL string concatenation is cleaner in comparison with other database management systems. For example, if you use PostgreSQL or Oracle, you have to use the string concatenation operator ||. In Microsoft SQL server, you use the addition arithmetic operator (+) to concatenate string values.
Besides using spaces for string concatenation, MySQL provides two other functions that concatenate string values: CONCAT and CONCAT_WS .
MySQL CONCAT function
The MySQL CONCAT function takes one or more string arguments and concatenates them into a single string. The CONCAT function requires a minimum of one parameter otherwise it raises an error.
The following illustrates the syntax of the CONCAT function.
The CONCAT function converts all arguments to the string type before concatenating. If any argument is NULL , the CONCAT function returns a NULL value.
The following statement concatenates two quoted strings: MySQL and CONCAT .
If you add a NULL value, the CONCAT function returns a NULL value as follows:
See the following customers table in the sample database.
To get the full names of contacts, you use the CONCAT function to concatenate first name, space, last name as the following statement:
MySQL CONCAT_WS function: Concatenate strings with a separator
MySQL provides a special form of the CONCAT function: CONCAT_WS function. The CONCAT_WS function concatenates two or more string values with a predefined separator.
The following illustrates the syntax of the CONCAT_WS function:
The first argument is the separator for other arguments: string1, string2, …
The CONCAT_WS function adds the separator between string arguments and returns a single string with the separator inserted between string arguments.
The following statement concatenates two string values: John and Doe, and separates these two strings by a comma:
The CONCAT_WS function returns NULL if and only if the first argument, which is the separator, is NULL . See the following example:
Unlike the CONCAT function, the CONCAT_WS function skips NULL values after the separator argument. In other words, it ignores NULL values.
The following statement constructs complete addresses using the CONCAT_WS function:
Here is the output result:
In this tutorial, you have learned how to use MySQL CONCAT and CONCAT_WS functions to concatenate one or more string value into a single string.
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.
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: