Problem: MySQL regular expression (limiter not working)

Question

Mod Notes: Do not delete/Do not close I asked this question before and the administrator closed it because they thought it was similar to another user's question. I looked at the thread they recommended to me and it didn't contain the kind of numerical issues I was having. How does this thread match an entire string with a regex?

---

My questions/questions: REGEXP returns a false positive.

SELECT '123456' REGEXP '[0-9]{1,4}' AS Test;

According to my reading, the curly braces {1,4} part means that it appears at least 1 time and at most 4 times. But from the above, the range [0-9] appears more often than 4, but the query returns 1 instead of 0. I have attached the screenshot. What am I missing? Thanks.

Screenshots of examples in Workbench

Answer 1

SELECT '123456' REGEXP '^[0-9]{1,4}$' AS Test;

With "anchoring" you are asking to match the entire string. The above operation will fail due to the limit of 4.

SELECT '123456' REGEXP '^[0-9]{1,}$' AS Test;

Passes because it allows at least digits.

SELECT 'zzz123456' REGEXP '^[0-9]{1,}$' AS Test; -- Fail
SELECT '123456' REGEXP '^[0-9]*$' AS Test;  -- pass
SELECT '' REGEXP '^[0-9]{1,}$' AS Test;  -- fail (too short)
SELECT '' REGEXP '^[0-9]+$' AS Test;  -- same as {1,}
SELECT 'abc123456def' REGEXP '[0-9]{1,4}' AS Test; -- pass (no anchor)
SELECT 'abc123456def' REGEXP '^[^0-9]+[0-9]{1,4}[^0-9]+$' AS Test;  -- fail
SELECT 'abc123456def' REGEXP '[^0-9]*[0-9]+[^0-9]*' AS Test;  -- pass

The last two include [^0-9], which means "any number except 0-9.

Detailed description^

• At the beginning of the of the regular expression, ^ will "anchor" the processing at the beginning: REGEXP "^x" means "starting with x"; REGEXP "x" succeeds if "x" is anywhere in the string.
• At the beginning of "character set", ^ means "not": REGEXP "x[0-9]" Find x followed by a number' REGEXP "x[^0-9]" Find the number that does not immediately follow .