How To Select the Max Numeric in a Varchar Column in MySQL

Published Date Author: , Posted October 2nd, 2015 at 9:43:49am

I needed to automate the addition of new users with a sequential member’s ID in WordPress. Additionally, there were non-numeric entries in that column that had to be ignored.

The wp_users.user_login column is a varchar(60) and so does not naturally handle numeric operations well at all.

The solution is a combination of REGEXP and cast():

SELECT max(cast(user_login as unsigned)) + 1 FROM wp_users WHERE user_login REGEXP '^[0-9]+$';

No comments as yet.

Leave Your Comment  Leave a comment

All fields marked with "*" are required.