CODEWITHSUNDEEP
mysqlsqldatabasesorting
Kevin Jung
Kevin Jung

Reputation: 3103

MySQL sort varchar as number issue

I have seen lots of question like this on SO and I tried to implement the answers into my particular situation but I am having no luck. Some help would be greatly appreciated.

Query

SELECT `avatar` from `users` ORDER BY ABS(`avatar`) ASC;

Result

+--------------------------------+
| avatar                         |
+--------------------------------+
| 0/1_default.jpg                |
| 1/3_483487-1440x900_qp8a5a.jpg |
| 1/122_default.jpg              |
| 1/321_default.jpg              |
| 1/25_wefvvv.jpg                |
| 1/1000_latest.jpg              |
| 2/12_wefwefwef.jpg             |
| 2/1_default.jpg                |
+--------------------------------+

I tried to sort by ABS and columns but unless I made a new column or a dedicated table, I cannot find a way to sort this the way I want it to.

Essentially, I want to sort it numerically and a desired outcome would be something like:

Desired result

0/1
1/3
1/25
1/122
1/321
2/1
2/12

From the searches on SO, I know there is the SUBSTR function but with the '/' in the middle, I am not sure how I can get it to sort properly.

Upvotes: 0

Views: 218

Answers (1)

peterm
peterm

Reputation: 92785

UPDATED If the format for avatar is fixed then you can do it like this

SELECT avatar
  FROM users
 ORDER BY 1 * SUBSTRING_INDEX(avatar, '/', 1),
          1 * SUBSTRING_INDEX(SUBSTRING_INDEX(avatar, '_', 1), '/', -1),
          SUBSTR(avatar, INSTR(avatar, '_') + 1)
  • 1 * SUBSTRING_INDEX(avatar, '/', 1) gets everything before first / and converts it to a numeric value
  • 1 * SUBSTRING_INDEX(SUBSTRING_INDEX(avatar, '_', 1), '/', -1) gets everything between / and first _ and converts it to a numeric value
  • and finally SUBSTR(avatar, INSTR(avatar, '_') + 1) gets everything as it is to right after _

Output:

+--------------------------------+
| avatar                         |
+--------------------------------+
| 0/1_default.jpg                |
| 1/3_483487-1440x900_qp8a5a.jpg |
| 1/25_wefvvv.jpg                |
| 1/122_default.jpg              |
| 1/321_default.jpg              |
| 1/1000_latest.jpg              |
| 2/1_default.jpg                |
| 2/12_wefwefwef.jpg             |
+--------------------------------+

Here is SQLFiddle demo

To get the max avatar value based on your sort order

SELECT avatar
  FROM users
 ORDER BY 1 * SUBSTRING_INDEX(avatar, '/', 1) DESC,
          1 * SUBSTRING_INDEX(SUBSTRING_INDEX(avatar, '_', 1), '/', -1) DESC,
          SUBSTR(avatar, INSTR(avatar, '_') + 1) DESC
 LIMIT 1

Output:

|             AVATAR |
----------------------
| 2/12_wefwefwef.jpg |

Here is SQLFiddle demo

Upvotes: 3

Related Questions