How to pull data that are in a specific pattern in MYSQL and PHP-CodePudding

I have a MySQL table and on of the column that contains username in different patterns. I am wondering is there a way to pull the usernames that (first 3 positions are in letter and last 4 positions are in number)? So I want to pull ABE0005, LKD0125, GHD0011, etc... See below for username examples.

username column:
ABE0005
LKD0125
905593580
GHD0011
903649229
BAN_SS_USER

This is how I pull data for username that starts with 9.

$query = mysqli_query($db, "SELECT * FROM `table_name` WHERE term = '2022' AND user LIKE '9%'");
    
$b_202310_3 = mysqli_num_rows($query);

CodePudding user response：

Use regexp in a where clause to achieve this.

where username regexp '^[A-Z]{3}[0-9]{4}$'

^ is the start of the string
[A-Z] is any uppercase alpha character
{3} is 3 characters of the preceding group/class
[0-9] is any single integer
{4} is 4 of previous group/class
$ is the end of the string

Demo: https://regex101.com/r/LFFc4f/1 (Note this tool is using PCRE, MySQL uses POSIX (e.g. something like \d won't work in MySQL but would in this tool))

Per update query should be:

SELECT * 
FROM `table_name` 
WHERE term = '2022' AND user REGEXP '^[A-Z]{3}[0-9]{4}$'