MySQL - sort comma separated string in column

Question

I have table:

+-----+------------+--------------+
| id  | title      | numbers      |
| 2   | Title 1    | 2,8,5        |
| 3   | Title 2    | 50,7,9,4     |
+-----+------------+--------------+

Is it possible to sort within the column? In this case in column numbers.

I need to output ordered numbers column as follows:

+-----+------------+--------------+
| id  | title      | numbers      |
| 2   | Title 1    | 2,5,8        |
| 3   | Title 2    | 4,7,9,50     |
+-----+------------+--------------+

Something like:

SELECT id, title, SORT_FUNC(numbers) from table

I was looking for some function in MySQL documentation, but I found nothing.

Storing values as csv is very bad db design. You should read about normalization. — Jens
– Jens, Commented Feb 3, 2016 at 9:27
Smells like bad database design. If you will transform this numbers column to the separate table having only one number in a column but multiple rows for the same id - it will significantly simplify the things. — Andrey Korneyev
– Andrey Korneyev, Commented Feb 3, 2016 at 9:27
There is no builtin string splitting function in MySQL. You can try to implement it by yourself using bunch of instr and substring. — Andrey Korneyev
– Andrey Korneyev, Commented Feb 3, 2016 at 9:30
@AndyKorneyev There are a few mentioned here in the manual but nothing that will do what is required — RiggsFolly
– RiggsFolly, Commented Feb 3, 2016 at 9:32

Kickstart · Accepted Answer · 2016-02-03 10:24:35Z

It is possible, but not really a good idea.

As an example, you can split a comma separated list up by generating a range of numbers and using that with SUBSTRING_INDEX to get each element. However the range of numbers needs to be as big as the max number of delimited values.

You could then use GROUP_CONCAT to join the list back together in the right order. Note that the order will be different depending on whether you have cast the split up values as numbers / integers or left them as strings.

SELECT id, title, GROUP_CONCAT(aNumber ORDER BY aNumber)
FROM
(
    SELECT id, title, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers, ',', tens.acnt * 10 + units.acnt + 1), ',', -1) AS UNSIGNED) AS aNumber
    FROM some_table
    CROSS JOIN
    (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN
    (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    WHERE LENGTH(numbers) - LENGTH(REPLACE(numbers, ',', '')) >= tens.acnt * 10 + units.acnt
) sub0
GROUP BY id, title;

Demonstrated here on SQL fiddle (if SQL fiddle decides to work):-

http://www.sqlfiddle.com/#!9/c9703ee/4

First select is casting the values as integers to sort them numerically, 2nd one isn't casting them but just leaving them as strings, hence the sort order is different.

Collectives™ on Stack Overflow

MySQL - sort comma separated string in column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related