Natural Sort in MySQL

Natural Sort in MySQL

I think this is why a lot of things are sorted by release date.

A solution could be to create another column in your table for the "SortKey". This could be a sanitized version of the title which conforms to a pattern you create for easy sorting or a counter.

How to natural sort “X-Y” string data, first by X and then by Y?

Assuming the parts on either side of the - are limited to 2 digits and 5 digits respectively, you can extract the two numeric values using SUBSTR (and LOCATE to find the - between the two numbers) and then LPAD to pad each of those values out to 2 and 5 digits to allow them to be sorted numerically:

SELECT *
FROM data
ORDER BY LPAD(SUBSTR(id, 2, LOCATE('-', id) - 2), 2, '0') DESC,
         LPAD(SUBSTR(id, LOCATE('-', id) + 1), 5, '0') DESC

Output (for my expanded sample):

id
W20-12457
W20-4617
W20-100
W19-1040
W18-40461
W4-2017

Demo on db-fiddle

If the values can have more than 2 or 5 digits respectively, just change the second parameters to LPAD to suit.

Natural Sorting SQL ORDER BY

This will do it:

SELECT value
FROM Table1
ORDER BY value REGEXP '^[A-Za-z]+$'
        ,CAST(value as SIGNED INTEGER)
        ,CAST(REPLACE(value,'-','')AS SIGNED INTEGER)
        ,value

The 4 levels of the ORDER BY:

1. REGEXP assigns any alpha line a 1 and non-alphas a 0
2. SIGNED INT Sorts all of the numbers by the portion preceding the dash.
3. SIGNED INT after removing the dash sorts any of the items with the same value before the dash by the portion after the dash. Potentially could replace number 2, but wouldn't want to treat 90-1 the same as 9-01 should the case arise.
4. Sorts the letters alphabetically.

Demo: SQL Fiddle

Mysql Natural sorting in varchar field

One workaround to your situation uses string operations to obtain the numerical subject code and use it for sorting.

SELECT
    subject_code
FROM yourTable
ORDER BY
    CAST(SUBSTR(subject_code,
                INSTR(subject_code, ' ') + 1) AS UNSIGNED)

However, you should really be storing the text and numerical code in separate columns.

Output:

Demo here:

Rextester

Natural sorting in SQL does not work

The difference between the example you cite and your own trial, is that your data combines numeric and non-numeric characters in the same field, so casting to an integer doesn't work (it just gives 0). You can visualize why the query isn't working for you by adding the order by expressions in your select list like this:

SELECT test
       ,test REGEXP '^[A-Za-z]+$'
       ,CAST(test as SIGNED INTEGER)
       ,CAST(REPLACE(test,'-','')AS SIGNED INTEGER)
FROM table1
ORDER BY test REGEXP '^[A-Za-z]+$'
        ,CAST(test as SIGNED INTEGER)
        ,CAST(REPLACE(test,'-','')AS SIGNED INTEGER)
        ,test

You'll see that they all evaluate to 0, which does not give you the sort order you're looking for.

You need to separate the numeric portion of your value from the non-numeric portion, and sort them separately. Something like this:

SELECT test    
FROM table1
ORDER BY substring_index(test,'-',1)
        ,CAST(substring(test, locate('-', test)+1)AS SIGNED INTEGER)
        ,test

You might need to adjust that if your actual data isn't as simple and straightforward as your example data.

---

Related Topics