241

I am using MySQL and MySQL Workbench 5.2 CE. When I try to concatenate 2 columns, last_name and first_name, it doesn't work :

select first_name + last_name as "Name" from test.student
codeforester
  • 39,467
  • 16
  • 112
  • 140
Roshan
  • 2,604
  • 2
  • 15
  • 13

5 Answers5

411

MySQL is different from most DBMSs' use of + or || for concatenation. It uses the CONCAT function:

SELECT CONCAT(first_name, ' ', last_name) AS Name FROM test.student

There's also the CONCAT_WS (Concatenate With Separator) function, which is a special form of CONCAT():

SELECT CONCAT_WS(' ', first_name, last_name) from test.student

That said, if you want to treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR in MySQL, you can set the PIPES_AS_CONCAT SQL mode.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Eugene Yarmash
  • 142,882
  • 41
  • 325
  • 378
  • 20
    Better answer, explains that MySQL doesn't use concatenation operators. – DonBecker Mar 21 '13 at 18:06
  • 1
    For those using `Doctrine`, I had to use single quotes for the space in `CONCAT`, and double quotes around the entire query. – craned Mar 16 '16 at 19:36
  • 4
    To set `PIPES_AS_CONCAT`: `SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',PIPES_AS_CONCAT');`. To unset: `SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'PIPES_AS_CONCAT', '');`. More examples at https://mariadb.com/kb/en/library/sql-mode/#examples – mivk Apr 22 '18 at 17:23
34

Try:

select concat(first_name,last_name) as "Name" from test.student

or, better:

select concat(first_name," ",last_name) as "Name" from test.student
ADW
  • 4,030
  • 17
  • 13
12

Use concat() function instead of + like this:

select concat(firstname, lastname) as "Name" from test.student
Harry Joy
  • 58,650
  • 30
  • 162
  • 207
5

Apart from concat you can also use concat_ws (concatenate with separator):

SELECT CONCAT_WS(' ', first_name, last_name) from test.student

This function has the added benefit of skipping null values.

See https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat-ws

gil.fernandes
  • 12,978
  • 5
  • 63
  • 76
3

That's not the way to concat in MYSQL. Use the CONCAT function Have a look here: http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#function_concat

Vithun
  • 360
  • 3
  • 5