Panos K. Panos K. - 2 years ago 146
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 Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download