how to concat two columns into one with the existing column name in mysql?
MysqlMysql Problem Overview
I want to concatenate two columns in a table with a existing column name using mysql.
An example: I am having a column FIRSTNAME
and LASTNAME
and so many columns also. I want to concatenate these two columns with the name of FIRSTNAME
only.
So I tried like this:
SELECT *, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`;
but it displaying the two fields with the name of FIRSTNAME
. one field is having normal values and another one is having concatenated values. I want only one column with those concatenate value. I can select single columns, but am having more than 40 columns in my table.
Is there any way to remove the original column using mysql itself?
Mysql Solutions
Solution 1 - Mysql
As aziz-shaikh has pointed out, there is no way to suppress an individual column from the *
directive, however you might be able to use the following hack:
SELECT CONCAT(c.FIRSTNAME, ',', c.LASTNAME) AS FIRSTNAME,
c.*
FROM `customer` c;
Doing this will cause the second occurrence of the FIRSTNAME
column to adopt the alias FIRSTNAME_1
so you should be able to safely address your customised FIRSTNAME
column. You need to alias the table because *
in any position other than at the start will fail if not aliased.
Hope that helps!
Solution 2 - Mysql
Instead of getting all the table columns using * in your sql statement, you use to specify the table columns you need.
You can use the SQL statement something like:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS FIRSTNAME FROM customer;
BTW, why couldn't you use FullName instead of FirstName? Like this:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS 'CUSTOMER NAME' FROM customer;
Solution 3 - Mysql
You can try this simple way for combining columns:
select some_other_column,first_name || ' ' || last_name AS First_name from customer;
Solution 4 - Mysql
Remove the *
from your query and use individual column names, like this:
SELECT SOME_OTHER_COLUMN, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`;
Using *
means, in your results you want all the columns of the table. In your case *
will also include FIRSTNAME
. You are then concatenating some columns and using alias of FIRSTNAME
. This creates 2 columns with same name.
Solution 5 - Mysql
Just Remove * from your select clause, and mention all column names explicitly and omit the FIRSTNAME column. After this write CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME. The above query will give you the only one FIRSTNAME column.
Solution 6 - Mysql
I am a novice and I did it this way:
Create table Name1
(
F_Name varchar(20),
L_Name varchar(20),
Age INTEGER
)
Insert into Name1
Values
('Tom', 'Bombadil', 32),
('Danny', 'Fartman', 43),
('Stephine', 'Belchlord', 33),
('Corry', 'Smallpants', 95)
Go
Update Name1
Set F_Name = CONCAT(F_Name, ' ', L_Name)
Go
Alter Table Name1
Drop column L_Name
Go
Update Table_Name
Set F_Name