Here are two options we can use to add an option to our sql_mode without wiping all existing options.

Suppose we want to add PIPES_AS_CONCAT to our existing sql_mode.

Option 1

We could do this:

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

That appends PIPES_AS_CONCAT without wiping our existing settings. It uses the CONCAT() function to concatenate the existing SQL mode options with the new option.

Just to be clear, here’s what my sql_mode looks like before running the above code:

SELECT @@sql_mode;

Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

We can see that PIPES_AS_CONCAT is nowhere to be found.

Now let’s add PIPES_AS_CONCAT to our sql_mode and then it again:

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

Result:

PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

It has now been added. But most importantly, none of the other options have been lost.

Option 2

Another way to do it is with the SYS.LIST_ADD() function:

SET @@sql_mode = SYS.LIST_ADD(@@sql_mode, 'PIPES_AS_CONCAT');
SELECT @@sql_mode;

Result:

PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Same result.

We can use SYS.LIST_DROP() to drop an item:

SET @@sql_mode = SYS.LIST_DROP(@@sql_mode, 'PIPES_AS_CONCAT');
SELECT @@sql_mode;

Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION