How to Concatenate Multiple columns in MySQL

In this tutorial, I show How you can concatenate multiple columns in MySQL.

You can simply do this programmatically by separately select fields from MySQL Table and store their values in the single variable after concat their values.

But you can make the above process little simpler by concatenating the values while selecting rows from DataBase Table.

Let’s take a simple example –

You have two columns – firstname, lastname within your DataBase Table you want to show both the columns values in a single string form. In case you can MySQL functions to combine the values of the columns.

There are two functions for doing this –

• CONCAT
• CONCAT_WS

Both functions work similar but have little difference.

---

Contents

1. CONCAT
2. CONCAT_WS
3. Using them in WHERE CLAUSE
4. Conclusion

---

1. CONCAT

This function is used to concatenate multiple columns or strings into a single one. Arguments are separated by a comma.

Syntax – 

CONCAT( column1, column2, ... )

OR

CONCAT ( string1, string2, ... )

For demonstration, I am using Users Table which has following records.

id	username	firstname	lastname
1	yssyogesh	Yogesh	Singh
2	sonarika	Sonarika	Bhadoria
3	vishal	Vishal	Sahu

Example

I am using this function to concatenate firstname, lastname columns and set it ALIAS to fullname.

SELECT 
username, 
CONCAT( firstname, " ", lastname ) AS fullname 
FROM users

Output

id	username	fullname
1	yssyogesh	Yogesh Singh
2	sonarika	Sonarika Bhadoria
3	vishal	Vishal Sahu

---

2. CONCAT_WS

The CONCAT_WS() function not only add multiple string values and makes them a single string value. It also let you define separator ( ” “, ” ,  “, ” – “,” _ “, etc.).

Syntax – 

CONCAT_WS( SEPERATOR, column1, column2, ... )

OR

CONCAT ( SEPERATOR, string1, string2, ... )

Example 

SELECT 
username, 
CONCAT_WS( " ", firstname, lastname ) AS fullname 
FROM users

Output

id	username	fullname
1	yssyogesh	Yogesh Singh
2	sonarika	Sonarika Bhadoria
3	vishal	Vishal Sahu

As I said at the start of the function you can define any other characters instead of space.

---

3. Using them in WHERE CLAUSE

You can use both of them in WHERE CLAUSE for selection based on condition.

Example

SELECT * 
FROM users 
WHERE CONCAT_WS(" ",firstname,lastname) = "Sonarika Bhadoria"

Output

id	username	firstname	lastname
2	sonarika	Sonarika	Bhadoria

Same you can do with CONCAT function.

---

4. Conclusion

This functions generally you can use when you have to show multiple columns values within the single string. You can specify your own separator values like – space, comma, dash, etc in the function.

This saves you some of the time on the programming side where you add the column values separately.

How to Concatenate Strings in MySQL with CONCAT()

MySQL has the CONCAT() function, which allows you to concatenate two or more strings. The function actually allows for one or more arguments, but its main use is to concatenate two or more strings.

In MySQL (and in any computer programming environment), string concatenation is the operation of joining character strings end-to-end.

Here’s an example:

SELECT CONCAT('Homer', ' ', 'Simpson') AS 'Full Name';

Result:

+---------------+
| Full Name     |
+---------------+
| Homer Simpson |
+---------------+

Note that I actually concatenated 3 strings here. I concatenated the first name, the last name, plus a space.

If I didn’t add the space it would’ve looked like this:

SELECT CONCAT('Homer', 'Simpson') AS 'Full Name';

Result:

+--------------+
| Full Name    |
+--------------+
| HomerSimpson |
+--------------+

Which may or may not be the result you’re looking for.

So if we apply this to a database, then the query might look something like this:

SELECT CONCAT(FirstName, ' ', LastName) AS 'Full Name'
FROM Individuals
WHERE IndividualId = '1';

Result:

+---------------+
| Full Name     |
+---------------+
| Homer Simpson |
+---------------+

If you’re concatenating more than two strings, and you need a space (or other separator), consider using the CONCAT_WS() function. This allows you to specify a separator to be used in between each string. You only need to specify the separator once, and it’s used on every string that’s concatenated, therefore saving you from having to re-type it between each string.

NULL Arguments

The CONCAT() function returns NULL if any argument is NULL.

Example:

SELECT CONCAT('Homer', NULL, 'Simpson') AS 'Full Name';

Result:

+-----------+
| Full Name |
+-----------+
| NULL      |
+-----------+

Binary Strings vs Nonbinary Strings

The MySQL documentation states:

If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL:

id       Name       Value
1          A          4
1          A          5
1          B          8
2          C          9

how do I get it into the following format?

id         Column
1          A:4,5,B:8
2          C:9

I think I have to use GROUP_CONCAT. But I'm not sure how it works.

 Answers

select id, group_concat(`Name` separator ',') as `ColumnName`
from
(
  select id, concat(`Name`, ':',
  group_concat(`Value` separator ',')) as `Name`
  from mytbl
  group by id, `Name`
) tbl
group by id;

Update Splitting in two steps. First we get a table having all values(comma separated) against a unique[Name,id]. Then from obtained table we get all names and values as a single value against each unique id 

Edit There was a mistake in reading question, I had grouped only by id. But two group_contacts are needed if (Values are to be concatenated grouped by Name and id and then over all by id). Previous answer was

select 
id,group_concat(concat(`name`,':',`value`) separator ',')
as Result from mytbl group by id

---

SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result 
FROM test GROUP BY ID

---

 SELECT id, GROUP_CONCAT(CONCAT_WS(':', Name, CAST(Value AS CHAR(7))) SEPARATOR ',') AS result 
    FROM test GROUP BY id

you must use cast or convert, otherwise will be return BLOB

result is

id         Column
1          A:4,A:5,B:8
2          C:9

MySQL: Use CONCAT to include text in SELECT results

The CONCAT function in MySQL allows SELECT statements to act like printf commands by mingling text and query results together.

For example, to generate a quick list of links in HTML from a table ‘links’ with the columns ‘url’ (http://www.tech-recipes.com) and ‘title’ (Tech-Recipes) so that the output looks like:

Tech-Blog

use a select statement like:

SELECT CONCAT('<a href="', url, '">', title, '</a><br />') FROM links;

The CONCAT function simply joins the comma-separated list of strings and expressions (in this case column results from the query: url and title). As with any SELECT statement, the result set can be varied with the use of an optional WHERE clause and other valid SQL syntax.

Mysql: Mass Find and Replace MySQL

<?php
/*

* MySQL Mass Find and Replace

*/


// Connect to your MySQL database.
$hostname = "localhost";
$username = "root";
$password = "password";
$database = "db_name";
mysql_connect($hostname, $username, $password);

// The find and replace strings.
$find = "what_i_need_to_find";
$replace = "what_i_need_to_replace_with";

// Test mode
$test_mode = false;

// Loop through all tables and columns
$loop = mysql_query("

    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cannot loop through database fields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
    if ($test_mode)
        echo "{$query['s']}<br/>";
    else
       mysql_query($query['s']);
} 
?>