MySQL REGEX – Extract Data from String
REGEXP_SUBSTR() was introduced in MariaDB 10.0.5 and MySQL 8.0.
That is what you need to locate and extract the XX00000000X.
REGEXP (as you used it) only returns true/false.
SUBSTRING_INDEX() needs the string.
If you cant upgrade to one of those, the best you can do is use
REGEXP to identify rows that have XX00000000X, then use your app code to extract it.