CODEWITHSUNDEEP
mysqlsql
Whatre Youdoing
Whatre Youdoing

Reputation: 73

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;

Upvotes: 0

Views: 120

Answers (3)

Abdul Rehman Kaim Khani
Abdul Rehman Kaim Khani

Reputation: 1170

select trim('.' from max(salary))   from TEST_EMPLOYEE  where salary like '._.' ; 
-- O/P 
-- 3

Upvotes: 0

FanoFN
FanoFN

Reputation: 7114

Something like this, maybe:

SELECT   *
FROM     user
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(userid,'.',2),'.',-1) DESC, LENGTH(userid) ASC;

Upvotes: 0

Nick
Nick

Reputation: 147166

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

Upvotes: 3

Related Questions