MYSQL REGEXP pattern which is the same as LIKE '%%'?-CodePudding

There is a MYSQL statement, using PHP as an example

eg. "SELECT * FROM member WHERE name LIKE '%" . $key . "%'";

And need to change to REGEXP

eg. "SELECT * FROM member WHERE name REGEXP '" . $key . "'";

The problem is , REGEXP does not allow when $key is empty string '', is there any pattern that include everything (include null , empty etc ... value) ?

Thank you

CodePudding user response：

The LIKE pattern '%' is equivalent to the regular expression '.*'. Both match zero or more of any character.

mysql> select '' like '%' as `match`;
 ------- 
| match |
 ------- 
|     1 |
 ------- 

mysql> select '' regexp '.*' as `match`;
 ------- 
| match |
 ------- 
|     1 |
 -------

However, neither will match an SQL NULL. NULL is not equal to anything (even another NULL).

mysql> select null like '%' as `match`;
 ------- 
| match |
 ------- 
|  NULL |
 ------- 
1 row in set (0.00 sec)

mysql> select null regexp '.*' as `match`;
 ------- 
| match |
 ------- 
|  NULL |
 -------

You have to use IS NULL to test for NULL.

mysql> select null is null as `match`;
 ------- 
| match |
 ------- 
|     1 |
 -------

CodePudding user response：

Sometimes it can be convenient with a condition inside the query syntax:

your_query_fn(
  "SELECT * FROM member
  WHERE (" . (!empty($key) ? "name REGEXP '". escape_regex_string_here($key) ."'" : " name = '' or name is null") . ");"
);