MySQL Alphanumeric Sorting

Question

I have tried many solutions, LENGTH, CAST, etc. I can't seem to come up with a workable solution to order alphabetically, then naturally (1, 2 .... 10, 11). Any help would be greatly appreciated! Not every result has a number, some only have 1 digit, some have 2 digits. I can get them sorted Alphabetically, I can get them sorted naturally 1-10, I can't seem to get them to do both.

name
Fish 1
Fish 2
Cats
Cats 11
Cats 4
Dogs 2
Dogs 10
Dogs 11

I would like it alphabetically first, then naturally (for the numbers) second.

The above table should output:

Cats
Cats 4
Cats 11
Dogs 2
Dogs 10
Dogs 11
Fish 1
Fish 2

*I have tried many solutions* - please add these to your question and avoid anyone duplicating what you've already done. If you were not combining two separate pieces of information in a single column your ordering would be trivial. — Stu, Jun 13 '22 at 15:17
Does this answer your question? [Natural Sort in MySQL](https://stackoverflow.com/questions/153633/natural-sort-in-mysql) — nbk, Jun 13 '22 at 15:19
Divide the column into two separate ones. For example `Dogs 11` should become `Dogs`, `11`. Then sorting is easy. — The Impaler, Jun 13 '22 at 15:26
@TheImpaler I can't split the columns, we are too far down the rabbit hole for that at this point lol — Josh Magness, Jun 13 '22 at 15:35
Can you share your queries relative to your latest attempts at the bottom of your post? @JoshMagness — lemon, Jun 13 '22 at 15:37
@JoshMagness in the link are more than two solutions for your problem — nbk, Jun 13 '22 at 15:40
"I can't split the columns" -> Why? What prevents you from doing this? To solve the reason why you can't split the columns makes much more sense than keeping your incorrect table structure and having issues with simple sorting. — Jonas Metzler, Jun 13 '22 at 15:47
Splitting the columns doesn't have to be done in the actual data/table, it can be done in your query. But, that will make the runtime ***significantly*** more costly, as it will scan the entire table and be unable to utilise indexes to minimise costs. — MatBailie, Jun 13 '22 at 16:01

nbk · Answer 1 · 2022-06-13T16:21:00.690

A simple solution to your problem is splitting your data and sorting it then, when no number is attached the + 0 guarantees that it comes first.

any order for the same name is usually not solvable

If you have also letters the second parameter has to be a CASE WHEN which will sort further

CREATE TABLE tab1
    (`name` varchar(7))
;
    
INSERT INTO tab1
    (`name`)
VALUES
    ('Fish 1'),
    ('Fish 2'),
    ('Cats'),
    ('Cats 11'),
    ('Cats 4'),
    ('Dogs 2'),
    ('Dogs 10'),
    ('Dogs 11')
;

SELECt 
name
FROM
tab1
ORDER BY SUBSTRING_INDEX(name,' ',1) ASC ,SUBSTRING_INDEX(name,' ',-1) + 0 ASC

| name    |
| :------ |
| Cats    |
| Cats 4  |
| Cats 11 |
| Dogs 2  |
| Dogs 10 |
| Dogs 11 |
| Fish 1  |
| Fish 2  |

db<>fiddle here

It should be noted that *(because the `+ 0` **implicitly** coerces part of the string to an integer)* this will treat `FISH X`, `FISH Y`, `FISH 0`, `FISH 0X`, etc, all as the same position and they will come out in a non-deterministic/arbitrary order. Perhaps also add the second part of the string itself to the ordering? https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a6264888ae7f509bdf0cb95d9adbd4e9 — MatBailie, Jun 13 '22 at 16:04

MySQL Alphanumeric Sorting

1 Answers1