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]+)$';
Tags: Mysql / Wordpress

Similar questions

When attempting to update a self-hosted WordPress site, I'm seeing "ASN1 unknown message digest algorithm"
I've got several WordPress sites that I self-host on a Windows Server. When attempting to perform the upgrade process, via /wp-admin/update-core.php?action=do-core-upgrade I see the following error: error:0D0890A1:asn1 encoding routines:ASN1_verify:unknown message digest algorithm It's entirely unclear to me where to begin resolving this issue. Int...
I am not seeing any content within WordPress after migration?
Last night I migrated a site to a live server using Duplicator, all seemed to work well. Today I just noticed that once logged in, WordPress is not displaying any content on the editor? Although the content is displaying on the front end? Any suggestions would be gratefully received...
Editor role isn't seeing specific post type posts with only post_type parameter
The editor role is able to open following URL: https://example.com/wp-admin/edit.php?booking-status=booked&post_type=booking But not able to open following URL: https://example.com/wp-admin/edit.php?post_type=booking I am facing this amazing problem as when I pass other parameters with post_type then it is opening custom post type edit screen b...
What is the random string I am seeing when I use get_query_var?
I am setting up an advanced search page with ACF fields with a custom post type in wordpress. In my example I am sending 'Students' in my url parameters as ?licenseType=Students *EDIT adding PHP code: I have registered my query_vars and set up my pre_get_posts filter When I view the query that is being created I see What is the random string that i...
Wordpress not seeing .htaccess rules
I'm currently building a small business website and I'm hosting on a home server, and have hit a roadblock with not being able to enhance security because of Wordpress not following any of the rules I've put into .htaccess. I would like to preferably block access to the wp-login.php or wp-admin from other IP's than mine. So I used this bit of code ...
How can I prevent certain custom roles from seeing other custom roles on the user list page?
So I am building a system with 3 different custom roles - Admins, Providers, and Users. Providers have access to users.php, or the list users page. However, they should not be able to see/edit/access Admins, or other Providers. They should be able to see/edit themselves obviously, and see/edit all Users. My question is how can I prevent Providers f...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.