How To Select the Max Numeric in a Varchar Column in MySQL
erics, 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]+$';
Categories: How-To's, Technology Cast, Char, Column, Filter, howto, Max, mysql, Numeric, Regex, regexp, select, sort, SQL, tips, Varchar, Where, WordPress
Leave Your Comment
All fields marked with "*" are required.