By default, two pipe characters (||) are treated as a logical OR operator in MariaDB. However, you can change this behaviour if you like.

You can update your sql_mode to include the PIPES_AS_CONCAT option, in which case two pipes will be treated as a concatenation operator.

There are quite a few ways to add this option to your sql_mode. You can add it explicitly. Or you can set your sql_mode to an option that sets it implicitly.

I run through these options below.

Add PIPES_AS_CONCAT Explicitly

If you don’t want to mess up your existing sql_mode settings, you can run the following code:

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

That adds PIPES_AS_CONCAT without removing anything else. I could have used 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.

Let’s check my existing sql_mode settings after running that code:

SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

We can see that PIPES_AS_CONCAT is included along with other options that already existed.

ANSI

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.

Note that this will remove any existing settings and use just those settings applicable to the ANSI option.

To demonstrate this, let’s take another look at my sql_mode after setting it to ANSI:

SET sql_mode='ANSI';
SELECT @@sql_mode;

Result:

REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

We can see that my sql_mode has completely changed.

DB2

Along the same lines, we can set our sql_mode to be more in tune with various DBMSs.

Here’s how to set it to use DB2 conventions:

SET sql_mode='DB2';
SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,DB2,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

Those are the options that align with DB2.

MaxDB

SET sql_mode='MAXDB';
SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

SQL Server

Use MSSQL to set it to use SQL Server conventions:

SET sql_mode='MSSQL';
SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

Oracle

SET sql_mode='ORACLE';
SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT

The SIMULTANEOUS_ASSIGNMENT option is only added when using MariaDB 10.3 and higher.

PostgreSQL

SET sql_mode='POSTGRESQL';
SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS