CODEWITHSUNDEEP
mysqlsql
Chandra Sekhar Biswal
Chandra Sekhar Biswal

Reputation: 1269

MySQL, Concatenate two columns

There are two columns in a MySQL table: SUBJECT and YEAR.

I want to generate an alphanumeric unique number which holds the concatenated data from SUBJECT and YEAR.

How can I do this? Is it possible to use a simple operator like +?

Upvotes: 124

Views: 296369

Answers (6)

Shafiqul Islam
Shafiqul Islam

Reputation: 5690

You can use mysql built in CONCAT() for this.

SELECT CONCAT(`name`, ' ', `email`) as password_email FROM `table`;

change field name as your requirement

then the result is

enter image description here

and if you want to concat same field using other field which same then

SELECT filed1 as category,filed2 as item, GROUP_CONCAT(CAST(filed2 as CHAR)) as item_name FROM `table` group by filed1

then this is output enter image description here

Upvotes: 30

Shamsdine Ndaw
Shamsdine Ndaw

Reputation: 21

I have two columns: prenom and nom so to concatenate into a column with name chauffeur_sortant I used this script:

SELECT date as depart, retour, duree_mission, duree_utilisation, difference, observation, concat( tb_chaufeur_sortant.prenom, ' ', tb_chaufeur_sortant.nom) as chauffeur_sortant, concat(tb_chaufeur_entrant.prenom, ' ', tb_chaufeur_entrant.nom) as chauffeur_entrant
FROM tb_passation 
    INNER JOIN tb_vehicule 
         ON tb_vehicule.id = tb_passation.id_vehicule
    INNER JOIN tb_chaufeur_sortant 
         ON tb_chaufeur_sortant.id = tb_passation.id_sortant
    INNER JOIN tb_chaufeur_entrant 
         ON tb_chaufeur_entrant.id = tb_passation.id_entrant WHERE tb_vehicule.id = '';

Upvotes: 1

Faridul Khan
Faridul Khan

Reputation: 2007

In query, CONCAT_WS() function.

This function not only add multiple string values and makes them a single string value. It also let you define separator ( ” “, ” , “, ” – “,” _ “, etc.).

Syntax –

CONCAT_WS( SEPERATOR, column1, column2, ... )

Example

SELECT 
topic, 
CONCAT_WS( " ", subject, year ) AS subject_year 
FROM table

Upvotes: 3

Saravanan
Saravanan

Reputation: 9

$crud->set_relation('id','students','{first_name} {last_name}');
$crud->display_as('student_id','Students Name');

Upvotes: -2

Vasanth
Vasanth

Reputation: 451

In php, we have two option to concatenate table columns.

First Option using Query

In query, CONCAT keyword used to concatenate two columns

SELECT CONCAT(`SUBJECT`,'_', `YEAR`) AS subject_year FROM `table_name`;

Second Option using symbol ( . )

After fetch the data from database table, assign the values to variable, then using ( . ) Symbol and concatenate the values

$subject = $row['SUBJECT'];
$year = $row['YEAR'];
$subject_year = $subject . "_" . $year;

Instead of underscore( _ ) , we will use the spaces, comma, letters,numbers..etc

Upvotes: 10

Mischa
Mischa

Reputation: 43298

You can use the CONCAT function like this:

SELECT CONCAT(`SUBJECT`, ' ', `YEAR`) FROM `table`

Update:

To get that result you can try this:

SET @rn := 0;

SELECT CONCAT(`SUBJECT`,'-',`YEAR`,'-',LPAD(@rn := @rn+1,3,'0'))
FROM `table`

Upvotes: 233

Related Questions