Reputation: 1739

MySQL combine two columns into one column

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?

Upvotes: 146

Answers (12)

Codemaker2015

Reputation: 15716

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;

Upvotes: 0

Aamir17072

Reputation: 17

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);

Upvotes: 0

Ravin

Reputation: 1

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

Upvotes: 0

Md. Jamal Uddin

Reputation: 792

table:

---------------------
| column1 | column2 |
---------------------
|   abc   |   xyz   |
---------------------

In Oracle:

SELECT column1 || column2 AS column3
FROM table_name;

Output:

table:

---------------------
| 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;

Output:

table:

---------------------
| column3           |
---------------------
| abc.xyz           |
---------------------

Upvotes: 1

Balaji Dongare

Reputation: 67

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;

Upvotes: 6

Ritesh Yadav

Reputation: 321

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

Upvotes: 0

sk juli kaka

Reputation: 470

It's work for me

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

Upvotes: 18

2NinerRomeo

Reputation: 2847

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.

Upvotes: 3

Sunil Acharya

Reputation: 1183

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

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

Try this...

Upvotes: 1

Oreniwa Babatunde

Reputation: 351

Try this, it works for me

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

Upvotes: 35

mattk

Reputation: 333

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)

Upvotes: 7

Gordon Linoff

Reputation: 1271111

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)

Upvotes: 233

MySQL combine two columns into one column

Answers (12)

Related Questions