MySQL combine two columns into one column


Mysql Problem Overview

I'm trying to find a way to combine two columns into one, but keep getting the value '0' in the column instead to the combination of the words.

These are what I've tried as well as others:

SELECT column1 + column2 AS column3
FROM table;

SELECT column1 || column2 AS column3
FROM table;

SELECT column1 + ' ' + column2 AS column3
FROM table;

Could someone please let me know what I'm doing wrong?

Mysql Solutions

Solution 1 - Mysql

My guess is that you are using MySQL where the + operator does addition, along with silent conversion of the values to numbers. If a value does not start with a digit, then the converted value is 0.

So try this:

select concat(column1, column2)

Two ways to add a space:

select concat(column1, ' ', column2)
select concat_ws(' ', column1, column2)

Solution 2 - Mysql

Try this, it works for me

select (column1 || ' '|| column2) from table;

Solution 3 - Mysql

It's work for me

SELECT CONCAT(column1, ' ' ,column2) AS newColumn;

Solution 4 - Mysql

This is the only solution that would work for me, when I required a space in between the columns being merged.

select concat(concat(column1,' '), column2)

Solution 5 - Mysql

If you are Working On Oracle Then:

SELECT column1 || column2 AS column3
FROM table;


If You Are Working On MySql Then:

SELECT Concat(column1 ,column2) AS column3
FROM table;

Solution 6 - Mysql

For the MySQL fans out there, I like the IFNULL() function. Other answers here suggest similar functionality with the ISNULL() function in some implementations. In my situation, I have a column of descriptions which is NOT NULL, and a column of serial numbers which may be NULL This is how I combined them into one column:

SELECT CONCAT(description,IFNULL(' SN: ', serial_number),'')) FROM my_table;

My results suggest that the results of concatenating a string with NULL results in a NULL. I have been getting the alternative value in those cases.

Solution 7 - Mysql

I have used this way and Its a best forever. In this code null also handled

ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' + ISNULL(LastName,'') as FullName 
FROM Customer

Try this...

Solution 8 - Mysql


| column1 | column2 |
|   abc   |   xyz   |

In Oracle:

SELECT column1 || column2 AS column3
FROM table_name;



| column3           |
| abcxyz            |

If you want to put ',' or '.' or any string within two column data then you may use:

SELECT column1 || '.' || column2 AS column3
FROM table_name;



| column3           |
|           |

Solution 9 - Mysql

convert(varchar, column_name1) + (varchar, column_name)

Solution 10 - Mysql

SELECT Column1 + ' - ' + Column2 AS 'FullName' FROM TableName                              

Solution 11 - Mysql

SELECT   CONVERT (nvarchar (10), Month(NextDate))+'-'+CONVERT (nvarchar (10), Year(NextDate)) as MonthOfYear, COUNT(CaseNo) as CountDisposal
FROM dbo.Main_Cause_List 
WHERE (DisposalState = 'Dispossed-off') 
GROUP BY Month(NextDate),Year(NextDate);

Solution 12 - Mysql

try to use coalesce() and concat() to combine columns in the SQL query.

Assume that you have 4 columns (id, name, phone_number, country_code) in a user table and you want to print phone number in this format: +countrycodephonenumber

Eg: 1, vishnu, 9961907453, 91 will return phone number as +919961907453.

You can use the following query to get the above result.

select coalesce(concat('+', country_code, phone_number)) from user;


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionaabView Question on Stackoverflow
Solution 1 - MysqlGordon LinoffView Answer on Stackoverflow
Solution 2 - MysqlOreniwa BabatundeView Answer on Stackoverflow
Solution 3 - Mysqlsk juli kakaView Answer on Stackoverflow
Solution 4 - MysqlmattkView Answer on Stackoverflow
Solution 5 - MysqlBalaji DongareView Answer on Stackoverflow
Solution 6 - Mysql2NinerRomeoView Answer on Stackoverflow
Solution 7 - MysqlSunil AcharyaView Answer on Stackoverflow
Solution 8 - MysqlMd. Jamal UddinView Answer on Stackoverflow
Solution 9 - MysqlRitesh YadavView Answer on Stackoverflow
Solution 10 - MysqlRavinView Answer on Stackoverflow
Solution 11 - MysqlAamir17072View Answer on Stackoverflow
Solution 12 - MysqlCodemakerView Answer on Stackoverflow