Order by specific index of comma separated list

Question

I have data stored in a comma-separated format and I would like to run a query so that the users are ordered by the second value in the column.

So for this sample data:

user     |  Data
________________________________________
player1   | 45471,2529,32196008193896,99
admin     |  1136,2595,17760808279311,95 
gamer     | 13495,2432,32196008193896,98
________________________________________

The order would be (2595-2529-2432) which is admin => player1 => gamer.

I highly recommend against storing delimited lists. See this question: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — AdamMc331, Jun 25 '15 at 18:41

score 0 · Accepted Answer · answered Jun 25 '15 at 18:53

As I mentioned in the comments, you should really try to avoid storing delimited lists like that.

However, you can write a query to parse out the second value by using the SUBSTRING_INDEX() function. This function takes a string, the delimiter character, and an integer whether or not to take from the left/right of the delimiter and for how many. In other words, to get everything left of the second comma, it would look like this:

SELECT SUBSTRING_INDEX(data, ',', 2)
FROM myTable;

Then, from that string, you want everything to the right of the first comma, so you'll have to nest the functions. (Note, this may hurt efficiency, but that's the downfall of delimited lists):

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 2), ',', -1)
FROM myTable;

Then, you can just order by that value:

SELECT *
FROM myTable
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 2), ',', -1) DESC;

Here is an SQL Fiddle example.

Order by specific index of comma separated list

1 Answers1