How to query mysql tables with regular expressions?

Question

I'm using a correct regex, already tested with other tools. But in mysql seems not to work properly. Say this data:

enter image description here

And I want to select all the records which start with 2 non-digits followed by a dot, followed by one digit followed by a sequence of .00 (ex. DE.1.00.00.00.00)

\D{2}\.\d?\.0{2}.0{2}.0{2}.0{2}

The above used alone normally works. But on mysql, a simple query like the following won't work. It will return a table with all the columns and without any error, but also without any value. Why?

SELECT *
FROM V35_CATEGORIE cat
WHERE cat.CategoriaCodice REGEXP '\D{2}\.\d?\.0{2}.0{2}.0{2}.0{2}'

• Rick James: What version of MySQL?

Answer 1

I answer by myself. In short, sql syntax above is correct, so how to query mysql tables with regular expressions? Exactly like I did.

The reason why it didn't work, is regexp syntax being too 'complex' for mysql. I found out that mysql doesn't support the full syntax possibilities of standard regular expressions. I must use simpler symbols/constructs. So for example, instead of \D{2} I can use [A-Z]{2}, and instead of \d, knowing I'll want 1 or 2, I can use [1,2]?. A syntax like the following will perfectly work in mysql to match a string like 'IT.1.12.00.00.00':

[A-Z]{2}\.[1,2]?\.[1-9][0-9](\.00){3}

With full regexp syntax I could have use lookahead assertions and other constructs, but anyway, for now for my needs I can use plain 'old' grammar.

• Rick James: The paragraph discusses what could be used in MySQL 8.0. The answer applies to any version.