CODEWITHSUNDEEP
sqlregexmariadb
G. Langlois
G. Langlois

Reputation: 75

How do I use REGEXP in MariaDB to find a specific pattern of usernames in my database

So I'm trying to find usernames that match a certain pattern, basically to exclude them from my query since those who follow that pattern are essentially automatically generated inactive users.

So here it goes.

These usernames follow this pattern: userID@accountID. So they can look something like this:

1@1234

The user ID can be anywhere from 1 to 3 digits long and the account ID can be anywhere from 2 to 5 digits long.

I've tried to do this, which I've found on another thread, but I think it only works for MySQL and not MariaDB. 

SELECT ...

FROM   ...

WHERE  username.Username NOT LIKE REGEXP '^[0-9]\{1\}@[0-9]\{1\}$' OR #For X@X
       username.Username NOT LIKE REGEXP '^[0-9]\{2\}@[0-9]\{1\}$' OR #For XX@X
       username.Username NOT LIKE REGEXP '^[0-9]\{3\}@[0-9]\{1\}$' OR #For XXX@X
       username.Username NOT LIKE REGEXP '^[0-9]\{1\}@[0-9]\{2\}$' OR #For X@XX
       username.Username NOT LIKE REGEXP '^[0-9]\{2\}@[0-9]\{2\}$' OR #For XX@XX
       username.Username NOT LIKE REGEXP '^[0-9]\{3\}@[0-9]\{2\}$' OR #For XXX@XX
       username.Username NOT LIKE REGEXP '^[0-9]\{1\}@[0-9]\{3\}$' OR #For X@XXX
       username.Username NOT LIKE REGEXP '^[0-9]\{2\}@[0-9]\{3\}$' OR #For XX@XXX
       username.Username NOT LIKE REGEXP '^[0-9]\{3\}@[0-9]\{3\}$' OR #For XXX@XXX
       username.Username NOT LIKE REGEXP '^[0-9]\{1\}@[0-9]\{4\}$' OR #For X@XXXX
       username.Username NOT LIKE REGEXP '^[0-9]\{2\}@[0-9]\{4\}$' OR #For XX@XXXX
       username.Username NOT LIKE REGEXP '^[0-9]\{3\}@[0-9]\{4\}$' OR #For XXX@XXXX
       username.Username NOT LIKE REGEXP '^[0-9]\{1\}@[0-9]\{5\}$' OR #For X@XXXXX
       username.Username NOT LIKE REGEXP '^[0-9]\{2\}@[0-9]\{5\}$' OR #For XX@XXXXX
       username.Username NOT LIKE REGEXP '^[0-9]\{3\}@[0-9]\{5\}$'    #For XXX@XXXXX

And it obviously doesn't work, hense why I'm asking the question here.

So my question is, what would the proper format be? Also, is there a way to not have 15 conditions and format the REGEXP so that only one condition does the work?

Upvotes: 0

Views: 586

Answers (1)

GMB
GMB

Reputation: 222512

The proper syntax is <expr> not regex <regex> (there is no like involved, neither in MySQL nor in MariaDB).

You would need to get rid of these backslashes. Also, you can use pairs of numbers (separated by a comma) as quantifiers to define the minimum and maximum number of matches. This lets you use a single condition, like:

where username not regexp '^[0-9]{1,3}@[0-9]{2,5}$'

This seems to work in MariaDB 10.2.

Upvotes: 1

Related Questions