MySQL order by string with numbers

Question

I have strings such as M1 M3 M4 M14 M30 M40 etc (really any int 2-3 digits after a letter) When I do " ORDER BY name " this returns:

M1, M14, M3, M30, M4, M40

When I want:

M1, M3, M4, M14, M30, M40 Its treating the whole thing as a string but I want to treat it as string + int

Any ideas?

Will there always only be one letter at the beginning of the string? — Zane Bien, Aug 23 '12 at 18:01

rocky3000 · Accepted Answer · 2012-08-23T22:43:16.603

16

You could use SUBSTR and CAST AS UNSIGNED/SIGNED within ORDER BY:

SELECT * FROM table_name ORDER BY
    SUBSTR(col_name FROM 1 FOR 1),
    CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)

edited Aug 23 '12 at 22:43

answered Aug 23 '12 at 18:04

rocky3000

1,134
8
9

works great thanks! Just changed FROM 1 FOR 2 to FROM 1 FOR 1 and works – Adam Esterle Aug 23 '12 at 19:01
1

@rocky what to do if there is no fix formate, possible string likes: test1,1test,te2st,test11,2131,10t,t22g – Er.KT Nov 13 '14 at 10:28

Zane Bien · Answer 2 · 2012-08-23T18:52:55.837

If there can be multiple characters at the beginning of the string, for example like 'M10', 'MTR10', 'ABCD50', 'JL8', etc..., you basically have to get the substring of the name from the first position of a number.

Unfortunately MySQL does not support that kind of REGEXP operation (only a boolean value is returned, not the actual match).

You can use this solution to emulate it:

SELECT   name
FROM     tbl
ORDER BY CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(name AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,1)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,2)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,3)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,4)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,5)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,6)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   SUBSTRING(name,1,7)
         END,
         CASE WHEN ASCII(SUBSTRING(name,1)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,1) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,2)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,2) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,3)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,3) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,4)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,4) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,5)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,5) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,6)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,6) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,7)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,7) AS UNSIGNED)
              WHEN ASCII(SUBSTRING(name,8)) BETWEEN 48 AND 57 THEN
                   CAST(SUBSTRING(name,8) AS UNSIGNED)
         END

This will order by the character part of the string first, then the extracted number part of the string as long as there are <=7 characters at the beginning of the string. If you need more, you can just chain additional WHENs to the CASE statement.

its worked but how to order if its have number values like: 'M10', '40', 'MTR10', 'ABCD50','8', 'JL8','55' — sytolk, Dec 08 '15 at 13:08
Its need to add ORDER BY name*1, CASE.. before the first CASE to work if its have clean numbers between mixed string with numbers. — sytolk, Dec 08 '15 at 13:28

score 2 · Answer 3 · answered Jul 16 '13 at 18:21

I couldn't get this working for my issue which was sorting MLS Numbers like below:

V12345 V1000000 V92832

The problem was V1000000 wasn't being valued higher than the rest even though it's bigger.

Using this solved my problem:

ORDER BY CAST(SUBSTR(col_name FROM 2) AS UNSIGNED) DESC

Just removed the SUBSTR(col_name FROM 1 FOR 1)

score 1 · Answer 4 · edited Aug 02 '17 at 15:08

1

You can use:

order by name,SUBSTRING(name,1,LENGTH(name)-1)

edited Aug 02 '17 at 15:08

Vadim Kotov

8,084
8
48
62

answered Aug 23 '12 at 18:07

user1619962

374
2
3

score 0 · Answer 5 · answered Apr 08 '13 at 12:35

It split number and letters as separately.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(col,'1', 1), '2', 1), '3', 1), '4', 1), '5', 1), '6', 1)
, '7', 1), '8', 1), '9', 1), '0', 1) as new_col  
FROM table group by new_col;

score 0 · Answer 6 · answered Nov 08 '16 at 13:19

0

Try remove the character with SUBSTR. Then use ABS to get the absolute value from field:

SELECT * FROM table ORDER BY ABS(SUBSTR(field,1));

answered Nov 08 '16 at 13:19

Bruno Gerotto

369
2
11

score 0 · Answer 7 · answered Jul 02 '20 at 10:23

0

Another method i used in my project is:

SELECT * FROM table_name ORDER BY LENGTH(col_name) DESC, col_name DESC LIMIT 1

answered Jul 02 '20 at 10:23

Simone Rossaini

8,115
1
13
34

MySQL order by string with numbers

7 Answers7

Linked