Panos K. Panos K. - 7 months ago 38
SQL Question

Regex MySQL, Find string with at least 2 letters and 2 numbers

I'm using this regular expression

REGEXP '^([a-z]){2,}([0-9]){2,}$'

to find strings with more than 2 letters and more than 2 numbers in combination with this
REGEXP '^([0-9]){2,}([a-z]){2,}$'
.
This covers only strings starting with numbers or ending with numbers.

I need one regex to find numbers in between letters also.

MySQL version 5.1.73

Answer

Assuming you want to test column val then try the following:

...
WHERE (val REGEXP '^[0-9a-z]*$')
     +(val REGEXP '[a-z].*[a-z]')
     +(val REGEXP '[0-9].*[0-9]')=3

This combination will test val to be made up of only numbers and characters (first regexp), contain at least two letters' (second regexp) and at least two numbers (third regexp).