In MySQL, list_drop() is a system function that removes a value from a comma-separated list of values and returns the result.

Syntax

The syntax goes like this:

list_drop(list, value)

Where list is the list that contains the value to remove, and value is the value to remove.

Example 1

Here’s a basic example to demonstrate the concept:

SELECT sys.list_drop('Cat,Dog,Cow,Bird', 'Bird');

Result:

Cat,Dog,Cow

So, the first argument is the list, and the second argument is the value that we dropped from that list.

Example 2

The list_drop() function can be useful for removing items from system variables that store a comma-separated list of values. For example, we can use list_drop() to drop a value from our sql_mode system variable.

Let’s first check the current value of sql_mode:

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

Suppose we want to remove the first item (PIPES_AS_CONCAT).

Here’s how we can do that with list_drop():

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

So we successfully removed the value from our sql_mode.

Non-Existent Values

If the value doesn’t exist in the list, nothing is removed, and list_drop simply returns the original list:

SELECT sys.list_drop('Cat,Dog,Cow,Bird', 'Fish');

Result:

Cat,Dog,Cow,Bird