16

I'm using sqlite3 for the moment, and hence concatenation strings using the || operator.

At some later date I'd like to shift to MySQL, and hence it would be nice if no changes to the code had to be made. I'd normally use concat() to concatenate in MySQL. Does || work too, or will I have to modify my code? Or is there any other solution?

I'm coding in Ruby on Rails 3.1, by the way.

codaddict
  • 445,704
  • 82
  • 492
  • 529
Rahul Sekhar
  • 2,761
  • 5
  • 23
  • 27

1 Answers1

31

The || works in MySQL as well but you need to set sql_mode to PIPES_AS_CONCAT.

Official Doc

Demo:

mysql> select c from tmp;
+------+
| c    |
+------+
| foo  |
| bar  |
+------+
2 rows in set (0.00 sec)

mysql> select c||' hi' from tmp;
+----------+
| c||' hi' |
+----------+
|        0 |
|        0 |
+----------+
2 rows in set, 2 warnings (0.00 sec)

mysql> set sql_mode=PIPES_AS_CONCAT;
Query OK, 0 rows affected (0.00 sec)

mysql> select c||' hi' from tmp;
+----------+
| c||' hi' |
+----------+
| foo hi   |
| bar hi   |
+----------+
2 rows in set (0.00 sec)
DataAlchemist
  • 187
  • 1
  • 2
  • 11
codaddict
  • 445,704
  • 82
  • 492
  • 529
  • This is also included when running in `ansi` mode (which is something I highly recommend): http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_ansi –  Nov 21 '11 at 13:34
  • The previous ANSI mode comment has a dead link; use https://dev.mysql.com/doc/refman/8.0/en/compatibility.html – Bret Weinraub Nov 17 '22 at 09:35