MySQL merges two columns into one column

Question

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

This is what I 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; 

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

Answer 1

I assume that you are using MySQL, where the + operator does the addition as well as silently converting values ​​to numbers. If the value does not start with a digit, then the converted value is 0 .

So try the following:

 select concat(column1, column2) 

Two ways to add a space:

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

Answer 2

It works for me

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

Answer 3

Try it works for me

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

Answer 4

This is the only solution that will work for me when I need the space between the columns to be merged.

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

Answer 5

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

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

My results show that concatenating strings with NULL results in NULL . I get alternative value in these cases.

Answer 6

If you are running Oracle Then:

 SELECT column1 || column2 AS column3 FROM table; 

OR

If you are working on MySql then:

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

Answer 7

I used this method and its best forever. In this code, null is also processed

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

Try it...

Answer 8

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

Answer 9

SELECT Collumn1 + '-' + Collumn2 AS FROM FullName TableName

Answer 10

- for example: columns with sets of numbers .... - When combining columns with numbers, you will need to drop these columns from the #data type into a row - another sql will add these numbers together to the same columns / result.

select a throw (shipperid as varchar) + '| '+ cast (empid as varchar) from sales.Orders