51

I don't know concatenation operator for MySQL.

I have tried this code for concatenation:

SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

But it didn't work. Which operator should I use to concatenate strings?

T.S.
  • 18,195
  • 11
  • 58
  • 78
user2201462
  • 981
  • 3
  • 8
  • 8
  • 1
    Today i have realized that something like "SELECT 'hello ' 'world' AS col1, ' from ' 'Mysql' AS col2 ....also works – Diego Andrés Díaz Espinoza Mar 19 '15 at 22:31
  • @DiegoAndrésDíazEspinoza: That's "concatenation of adjacent string literals", isn't it? That is, it works when you're concatenating two string literals (perhaps because they're too long to fit on one line). Does it also work if you try to concatenate a column with a string literal, as in the question? I'd expect that the answer is 'no'. – Jonathan Leffler Feb 16 '17 at 19:13
  • @JonathanLeffler yes, it is adjacent concatenation of string literal, that is why it was just a comment, not an answer. Attending to answers below it is not possible adjacent concatenation, it is just possible while using CONCAT. – Diego Andrés Díaz Espinoza Feb 22 '17 at 19:10

6 Answers6

79

|| is the ANSI standard string concatenation operator, supported by most databases (notably not MS SQL Server). MySQL also supports it, but you have to SET sql_mode='PIPES_AS_CONCAT'; or SET sql_mode='ANSI'; first.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
200_success
  • 7,286
  • 1
  • 43
  • 74
59

You were using ORACLE type of concatenation. MySQL's Should be 

 SELECT CONCAT(vend_name, '(', vend_country, ')')

Call the CONCAT() function and separate your values with commas.

codingbiz
  • 26,179
  • 8
  • 59
  • 96
14

MySQL CONCAT function is used to concatenate two strings to form a single string. Try out following example:

mysql> SELECT CONCAT('FIRST ', 'SECOND');
+----------------------------+
| CONCAT('FIRST ', 'SECOND') |
+----------------------------+
| FIRST SECOND               |
+----------------------------+
1 row in set (0.00 sec)

To understand CONCAT function in more detail consider an employee_tbl table which is having following records:

mysql> SELECT CONCAT(id, name, work_date)
    -> FROM employee_tbl;
+-----------------------------+
| CONCAT(id, name, work_date) |
+-----------------------------+
| 1John2007-01-24             |
| 2Ram2007-05-27              |
| 3Jack2007-05-06             |
| 3Jack2007-04-06             |
| 4Jill2007-04-06             |
| 5Zara2007-06-06             |
| 5Zara2007-02-06             |
+-----------------------------+
Saic Siquot
  • 6,513
  • 5
  • 34
  • 56
Akshay Joy
  • 1,765
  • 1
  • 14
  • 23
5

Whats good about using concat is that you can pass different data type columns and concat string representations

 SELECT concat('XXX',  10.99, 'YYY', 3, 'ZZZ', now(3)) as a;

Output

a
-----
XXX10.99YYY3ZZZ2018-09-21 15:20:25.106

T.S.
  • 18,195
  • 11
  • 58
  • 78
2

Simply you can use CONCAT keyword to concatenate the Strings.. You can use it like

SELECT CONCAT(vend_name,'',vend_country) FROM vendors ORDER BY name;
Kumar Anil Chaurasiya
  • 341
  • 2
  • 13
-1

You have to set pipes as concat every time before you run a query using pipes as a concatenate operator.

Xeno
  • 1