SQLite Order Rows by Last Characters in a String

Question

I'm using an sqlite database for my Java application and I have a single varchar column with a bunch user stats that is written, read, and parsed by my Java program. What I want to do is have an query that can sort the rows by the last stat in the column. The stats are separated by commas and their lengths vary so I need something that can take the whole last section of the text (which is the text from the last comma to the end of the data) and order that. This would be easy to do just within my Java application but much more resource intensive which is why I would like to do this directly with the query. In practice the actual column data looks something like this:

2015/7/4 17:24:38,[(data1, 1, 1436394735787)|(data2, 4, 1436394739288)], 5

and I'm trying to order the rows based on that last 5 or whatever else it might be (it can be multiple digits too). Iv'e tried almost everything I could find on the internet but a lot of issues if had were because of syntax errors (even when I copied the query exactly) or problems where a specific function doesn't exist and i'm not really sure what the cause of those errors is. I'm not really familiar with MySQL so a simple answer would be the most appreciated.

Answer 1

As a quick and hacky solution (low performance if you have huge amount of data):

SELECT * FROM [tbl] ORDER BY CAST(SUBSTR([col], INSTR([col], '],') + 2) AS INTEGER);

But as Hayley suggested, to re-evaluate the data, you can use:

INSERT INTO [new-tbl]
SELECT SUBSTR(val, 0, c1),
       SUBSTR(val, c1+1, c2-c1),
       CAST(SUBSTR(val, c2+2) AS INTEGER),
       [more-cols]
FROM (
    SELECT INSTR([col], ',') AS c1,
           INSTR([col], '],') AS c2,
           [col] AS val,
           [more-cols]
    FROM [tbl]);