From list of 99999 sequential numbers, what regex would find only those with two 3s and two 7s?-CodePudding

I'm querying a MySQL database of 70,000 sequential numbers. What search term will find all those with exactly two 3s and exactly two 7s? For example, 37173 or 3377 or 3737?

CodePudding user response：

for mysql 8.0 you can use regexp_replace, something like:

SELECT *
FROM mytable
WHERE concat(regexp_replace(mycolumn,'[^3]',''), regexp_replace(mycolumn,'[^7]','')) = '3377'

CodePudding user response：

MySQL regular expressions don't support lookahead expressions. Here's a solution that works, using two conditions:

SELECT ...
FROM mytable
WHERE mycolumn RLIKE '3.*3' AND mycolumn RLIKE '7.*7'

One could probably make a single regular expression that searched all possible orderings of the digits, but the above is way easier to read.