Seeing if value at first index is a number
I am working on a Wordpress site where 100,000+ spam accounts have been made. Although there are other types as well but many of them seem to have a user name that starts with a number.
So I wanted to ask whether there can be a
MYSQL
query to
select/delete
all users whose username starts with a number.
An extension to this question is that whether those users need to be deleted from the users table only or also the user-meta or other tables.
Any help appreciated.
Solutions
Yep, there is (of course) MySQL query for almost everything, below query selects such users, you just need to run it couple of times:
SELECT * FROM wp_users WHERE user_login LIKE 'put_number_here%' OR user_nicename LIKE LIKE 'put_number_here%' OR display_name LIKE 'put_number_here%'
Replace the
put_number_here
with starting number and go on.
Then, after assuring that those users are not legit, you can delete them with almost the same query:
DELETE FROM wp_users WHERE user_login LIKE 'put_number_here%' OR user_nicename LIKE LIKE 'put_number_here%' OR display_name LIKE 'put_number_here%'
EDIT: Here is the user defined function, which checks if the given value from a field is numeric.
CREATE FUNCTION ISNUMERIC(myVal VARCHAR(1024))
RETURNS TINYINT(1) DETERMINISTIC
RETURN myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';