MySQL alphabetically-number order

Question

I need to order data of a query alphabetically, but in some elements there are digits and ordering is not "natural".

eg:

TABLE
products_id
products_name
product_price 
...

The value of field products_name is:

product 1
product 2
product 10
another 4 product
new prod 2
new prod 21
new prod 10

I need to order the records like this:

another 4 product
new prod 2
new prod 10
new prod 21
product 1
product 2
product 10

Can anyone can help me? Thank you.

As I guess those are not real product name you are likely to get a lot of misleading comment/answers — RiggsFolly, Jul 27 '16 at 15:36
If there are leading numbers you want them first or last? Do you have a query with `order` that you are having issues with? Maybe you are talking about http://stackoverflow.com/questions/17418215/order-by-alphabet-first-then-follow-by-number.. — chris85, Jul 27 '16 at 15:36
i think you will have to split the product name into `a string bit` and `a number bit` in your query in MYSQL then sort on the 2 columns `ORDER BY Stringybit, Numberybit` — RiggsFolly, Jul 27 '16 at 16:02
What you want is a 'natural sort'. There are ways to do this in PHP or MySQL. http://php.net/manual/en/function.natsort.php — Rick Burns, Jul 27 '16 at 16:34

score 0 · Answer 1 · answered Jul 27 '16 at 16:26

The correct solution would be to normalise your design, but just for fun...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (x VARCHAR(20) NOT NULL);

INSERT INTO my_table VALUES
('product 1'),
('product 2'),
('product 10'),
('another 4 product'),
('new prod 2'),
('new prod 21'),
('new prod 10');

SELECT *,SUBSTRING_INDEX(x,' ',1) a, SUBSTRING_INDEX(x,' ' ,-1) b FROM my_table ORDER BY a,b+0;
+-------------------+---------+---------+
| x                 | a       | b       |
+-------------------+---------+---------+
| another 4 product | another | product |
| new prod 2        | new     | 2       |
| new prod 10       | new     | 10      |
| new prod 21       | new     | 21      |
| product 1         | product | 1       |
| product 2         | product | 2       |
| product 10        | product | 10      |
+-------------------+---------+---------+

Thats basically what I was [talking about](http://stackoverflow.com/questions/38617460/mysql-alphabetically-number-order/38617870?noredirect=1#comment64621899_38617460) but you need to seperate all the text from the trailing numbers not just to the first space — RiggsFolly, Jul 27 '16 at 17:30

Dallas Caley · Answer 2 · 2016-07-27T16:29:32.893

-1

note: this is not exactly what you are asking for but it may be good enough

The best way to accomplish a natural sort is to first sort by length then by value, like this

ORDER BY LENGTH(products_name), products_name

Ok here's another ridiculus solution, (but it works)

SELECT * FROM (
SELECT test,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    test, 0, ''), 1, ''), 2, ''), 3, ''), 4, ''), 5, '')
    , 6, ''), 7, ''), 8, ''), 9, '') AS derp 
FROM zzz) t1
ORDER BY derp, LENGTH(test), test

Note, in order to test my solution i actually created a table called zzz with a varchar field called test and put the actual data in it. it works exactly as the OP asked.

edited Jul 27 '16 at 16:29

answered Jul 27 '16 at 15:53

Dallas Caley

5,367
6
40
69

1

surely that wont do anything useful. How will that cope with `A product name 10` and `A product name 2` Answer "it wont" – RiggsFolly Jul 27 '16 at 15:57
It's intended to solve the problem when digits are appended to the end of a string, so imaging you have a bunch of values that all start with "product" with the numbers 1 to 1000 appended. In that case the items will be sorted correctly instead of the 1, 10, 100 and 1000 all being next to each other. Like i said its not a perfect solution but at least it gets the similar products sorted correctly – Dallas Caley Jul 27 '16 at 16:04
Wait i must be a moron, your example is EXACTLY what my solution solves. 'A product name 2' comes before 'A product name 10' becuase it has a shorter length. – Dallas Caley Jul 27 '16 at 16:24
it works because, you first remove all of the numbers with the REPLACE's (no Regex in MySQL), now you have a column which can be sorted alphabetically ignoring the numbers so you do that sort first, then you sort by length of the actaul data column and then finally by the data column as my original natural sort mechanism showed. – Dallas Caley Jul 30 '16 at 17:04

score -2 · Answer 3 · edited Jul 27 '16 at 16:09

-2

you should use ORDER BY on your sentence

ORDER BY products_name ASC (or DESC as you want)

edited Jul 27 '16 at 16:09

Jay Blanchard

34,243
16
77
119

answered Jul 27 '16 at 15:48

Luis Esteban

21
5

1

to quote the OP from a deleted similiar answer `That won't work, because it's not going to sort numbers. "10" will come before "2" alphabetically, but for numerical sequence that's wrong` – RiggsFolly Jul 27 '16 at 15:57

MySQL alphabetically-number order

3 Answers3