My SQL table structure looks like this

Here I want to filter all IDs which consist of M or L sizes. I tried this operation with CONCAT and REGEXP, I tried this statement with PHP

$stmt = $conn->prepare('SELECT * FROM table WHERE CONCAT(",", available_sizes, ",") REGEXP ",(?|?),"');

$param1 = "M";
$param2 = "L";

$stmt->bind_param("ss", $param1, $param2);
$stmt->execute();
$result = $stmt->get_result();

Bt it shows an error: 'repetition-operator operand invalid' from regexp query.

CodePudding user response：

You may match with REGEXP against \b(M|L)\b. Build the entire alternation in PHP and then bind it to a placeholder in the MySQL query:

$stmt = $conn->prepare("SELECT * FROM table WHERE available_sizes REGEXP ?");
$params = array("M", "L");
$regex = "[[:<:]](" . implode("|", $params) . ")[[:>:]]";

$stmt->bind_param("s", $regex);
$stmt->execute();
$result = $stmt->get_result();

Note: On MySQL 8 , use the above with the following minor change:

$regex = "\\b(" . implode("|", $params) . ")\\b";

CodePudding user response：

Your problem is because it does not say in which table in the REGEXP part.

SELECT * FROM table WHERE yourcolumn REGEXP your expression;