How to Enable the Pipe Concatenation Operator in MySQL

by

MySQL supports the use of the pipe concatenation operator (||) for concatenating its operands. However, you need to enable it first.

By default, MySQL treats || as a logical OR operator (although this treatment is currently deprecated). However, the ANSI standard requires that || is a concatenation operator. Perhaps you have code that already uses the pipe concatenation operator, and you would rather not go through and change the code to use the CONCAT() function.

Fortunately, MySQL provides us with the ability to specify whether to treat it as a logical OR operator or a concatenation operator.

You can enable || as a concatenation operator by setting the applicable option in your sql_mode.

You have two options:

  • Add PIPES_AS_CONCAT to your sql_mode
  • Set sql_mode to ANSI.

Option 1

Here, I add PIPES_AS_CONCAT to my sql_mode:

SET sql_mode=(SELECT CONCAT(@@sql_mode,',PIPES_AS_CONCAT'));

It’s true that I could have just gone sql_mode = 'PIPES_AS_CONCAT', but I would have lost all existing options. Using the above technique ensures that I don’t lose any existing options.

Option 2

Another way to do it is to set the sql_mode to ANSI:

SET sql_mode='ANSI';

ANSI mode changes syntax and behaviour to conform more closely to standard SQL.

Check your sql_mode

You can check your sql_mode like this:

SELECT @@sql_mode;

Result:

REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI

That’s the result I get after changing to ANSI mode. We can see the PIPES_AS_CONCAT is included in the list of options.

Concatenation Example

After setting the sql_mode using one of the above techniques, we can now use || as a pipe concatenation operator:

SELECT 'Homer' || 'Symptom';

Result:

HomerSymptom

If we hadn’t enabled the pipe concatenation operator, we would most likely have gotten an unexpected result, with a warning.