Is checking for optional characters required in mysql REGEXP? I have a few rows, in a users table with a name column:

SELECT name FROM users;
+--------------------+
| name               |
+--------------------+
| Administrator Core |
| Marketing Guy      |
+--------------------+


Now I want to check if the name column contains specified strings. For example, I want to check if 'Mark' or 'ket' is present in any row, and if so return it.

Both of these seem to work:

SELECT * FROM users WHERE LOWER(name) REGEXP 'ket|Mark'\G;

SELECT * FROM users WHERE LOWER(name) REGEXP '(.+)?ket(.+)?|(.+)?Mark(.+)?'\G;


Does the first one implicitly check for optional characters around ket and Mark? That's not how it would work with many programming languages. That is why I ask.
From the quite clear examples here, I performed a few tests (below).
The tests give 1 for true (i.e. a match) and 0 for false (no match).

Note the use of ^ and $ for the start and end of the REGEXP.

To answer your specific question, your first REGEXP doesn't care about any characters around 'ket' or 'Mark' - it just checks to see if either exists anywhere in the string and returns 1 if there is any match. '^Mark' will search for 'Mark' and 'Market' and any other string beginning with 'Mark'. The same thing happens for the $ for the end of strings. 

Check out the examples and try and follow them - Regular expressions are a great string to your SQL bow and it is very worthwhile learning them.

mysql&gt; SELECT 'Mark' REGEXP '^Market|.*katerina.*';
+--------------------------------------+
| 'Mark' REGEXP '^Market|.*katerina.*' |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.01 sec)

mysql&gt; SELECT 'Market' REGEXP '^Market|.*katerina.*';
+----------------------------------------+
| 'Market' REGEXP '^Market|.*katerina.*' |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql&gt; 

mysql&gt; SELECT 'Market' REGEXP 'ket|Mark';
+----------------------------+
| 'Market' REGEXP 'ket|Mark' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql&gt; SELECT 'Market' REGEXP 'ket$|Bark';
+-----------------------------+
| 'Market' REGEXP 'ket$|Bark' |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql&gt; SELECT 'Market' REGEXP 'ket|Bark';
+----------------------------+
| 'Market' REGEXP 'ket|Bark' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql&gt; SELECT 'Market' REGEXP '^ket|Bark';
+-----------------------------+
| 'Market' REGEXP '^ket|Bark' |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

mysql&gt; SELECT 'Market' REGEXP '^ket$|Bark';
+------------------------------+
| 'Market' REGEXP '^ket$|Bark' |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

mysql&gt;