How to Select Max Numeric from Varchar Field in MYSQL

select userid from user;

+--------+
| userid |
+--------+
| .1.    |
| .1.1.  |
| .2.    |
| .3.    |
| .3.1.  |
| .3.1.1 |
+--------+

​x
 
select userid from user;​+--------+| userid |+--------+| .1.    || .1.1.  || .2.    || .3.    || .3.1.  || .3.1.1 |+--------+​

Here I want to select the maximum number (which is 3) from the first number.

select max(userid) as userid from user where userid like ".%.";

+--------+
| userid |
+--------+
| .3.1.  |
+--------+

 
select max(userid) as userid from user where userid like ".%.";​+--------+| userid |+--------+| .3.1.  |+--------+​

But, that’s not what I want. The output I want is 3 without the dot at both side of the number. I want the fourth value of the

select userid from user;

 
select userid from user;​

Answer

You can use SUBSTRING_INDEX to extract the first number from each userid string, and then take the MAX of those:

SELECT MAX(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(userid, '.', 2), '.', -1) AS UNSIGNED)) AS userid
FROM user

 
SELECT MAX(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(userid, '.', 2), '.', -1) AS UNSIGNED)) AS useridFROM user​

Note that you need to CAST the value to an integer to ensure that e.g. 11 sorts higher than 3.

Output