Assume that you have a list of numeric values mixed with numeric

values appended with characters – like 2a, 12, 5b, 5a, 10, 11, 1, 4b.

Sorting on a list like this would usually end up so:

mysql> SELECT version -> FROM version_sorting -> ORDER BY version; +---------+ | version | +---------+ | 1 | | 10 | | 11 | | 12 | | 2a | | 4b | | 5a | | 5b | +---------+ 8 rows in set (0.03 sec)

To sort the list based on the numeric values, simply do this:

mysql> SELECT version -> FROM version_sorting -> ORDER BY CAST(version AS UNSIGNED), version; +---------+ | version | +---------+ | 1 | | 2a | | 4b | | 5a | | 5b | | 10 | | 11 | | 12 | +---------+ 8 rows in set, 4 warnings (0.00 sec)

That is really cool. Do you know of any way to do it if the reverse is true. So here is my list:

BG1

BG11

BG12

BG13

BG2

BG3

BG4

Do you know how I can order those correctly?

Thanks

Mike

Really good way to sort data

Thanks..

Works ðŸ˜€

i have a colum with:

1-200D1

1-200A1

2-200D1

2-200A1

4-200A1

4-200D1

3-200A1

3-200D1

it’s possible to get order like:

1-200D1

1-200A1

2-200D1

2-200A1

3-200D1

3-200A1

4-200D1

4-200A1

Best Regards,

Marco

I have a column with,

10 days

2 days

8 days

6 days

it’s perfectly sorted.

Thank you…

Danke! Funktioniert super. Danke danke.

i need to sort the values given

1

1 a

2

2a

5

apple

ball

xerox

This seems to be the default sorting anyway:

I have data mixed alphanumeric emails column in mysql like 1testproject3ert@gmail.com

1!fgfg@gmail.com

test1project1@gmail.com,

test10project20@gmail.com,

So its any combination of letter,digits,special characters before @

So I want to sort these emails like

1testproject3ert@gmail.com

1!fgfg@gmail.com

test1project1@gmail.com,

test10project20@gmail.com,

in natural order please help me

Thank you. This was very helpful!

hi, i need to sort :

A.1

B.1.1

B.1.2

C.1

D.1.1

D.1.2

D.1.3

D.2.1.1

D.2.1.2

D.3

D.4

….

D.9

D.10

D.11

D.12

Thanks for help

SELECT MyField

FROM MyTable

order by

IF( MyField REGEXP ‘^-?[0-9]+$’ = 0,

9999999999 ,

CAST(MyField AS DECIMAL)

), MyField

SELECT Square

FROM Table1

ORDER BY

CASE WHEN Square REGEXP ‘^[A-Z]{2}’

THEN 1

ELSE 0

END ASC,

CASE WHEN Square REGEXP ‘^[A-Z]{2}’

THEN LEFT(Square, 2)

ELSE LEFT(Square, 1)

END ASC,

CASE WHEN Square REGEXP ‘^[A-Z]{2}’

THEN CAST(RIGHT(Square, LENGTH(Square) – 2) AS SIGNED)

ELSE CAST(RIGHT(Square, LENGTH(Square) – 1) AS SIGNED)

END ASC