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 |
+--------+

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.  |
+--------+

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;

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

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

Output

3

Demo on SQLFiddle