Problem: MySQL regular expression (limiter not working)

Question

Mod Note: Do Not Remove/Do Not Close I asked this question before and the admins 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 doubt/problem: REGEXP returns false positives. SELECT'123456'REGEXP'[0-9]{1,4}'ASTest; 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 above

P粉242535777 · Answer

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 .