There are many times you need to concatenate the values of two or more columns while querying the result. To concatenate columns in MySql there is in-built function for that.
Let’s take a simple scenario where you store firstname and lastname of user in some table. Now you want to concatenate the result of firstname and lastname and show them as a name.
To concatenate two values in mysql. Mysql provides concat function.
How to concatenate column in MySql
Let’s i explain this.
First create table name users. In user table i create three columns id, firstname , lastname.
|
1 2 3 4 5 6 |
create table users ( id int(10) NOT NULL AUTO_INCREMENT, firstname varchar(200), lastname varchar(200), PRIMARY KEY(id) ); |
Insert some dummy values.
|
1 2 3 |
insert into users (firstname,lastname) values('John','Doe'); insert into users (firstname,lastname) values('John','Smith'); |
After running the above queries, table is created and it look like.
| id | firstname | lastname |
|---|---|---|
| 1 | John | Doe |
| 2 | John | Smith |
Syntax of concat function-
|
1 |
concat(str1,str2,str3,....,strn) |
concat(col1,”,col2);
|
1 2 3 |
// concatenate firstname and lastname with space. SELECT concat(firstname,' ',lastname) as user_name FROM users; |
Output
| user_name |
|---|
| John Doe |
| John Smith |
CONCAT_WS to Concatenate Columns with Separator
Syntax-
|
1 |
CONCAT_WS(separator,str1,str2,.....,strn) |
concat_ws functon concatenates the value with separator you specify.
|
1 |
select concat_ws(',',firstname,lastname); |
It returns the concatenate value of firstname and lastname with comma separated.
