Sort By Natural Ordering

Question

You are given a column of strings, sort them in natural order.

Answer

Natural ordering is simply the ordering that a human would expect.

Create Table

CREATE TABLE documents (
    -- string variable
    name varchar(255)
)

Insert Values Into Table

-- Insert into the table documents
INSERT INTO documents (name)
-- a list of files (notice the numbering)
VALUES
    ('file1.txt'),
    ('file2.txt'),
    ('file3.txt'),
    ('file4.txt'),
    ('file5.txt'),
    ('file6.txt'),
    ('file7.txt'),
    ('file8.txt'),
    ('file9.txt'),
    ('file10.txt'),
    ('file11.txt'),
    ('file12.txt'),
    ('file13.txt'),
    ('file14.txt'),
    ('file15.txt')

View Table

-- View all the rows
SELECT * FROM documents
name
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt
file7.txt
file8.txt
file9.txt
file10.txt
file11.txt
file12.txt
file13.txt
file14.txt
file15.txt

View Table Sorted In Non-Natural Order

Notice that simply using ORDER BY creates a non-natural ordering.

-- View all the rows
SELECT * FROM documents
-- Order them alphanumerically
ORDER BY name
name
file10.txt
file11.txt
file12.txt
file13.txt
file14.txt
file15.txt
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt
file7.txt
file8.txt
file9.txt

View Table Sorted In Natural Order

The solution is to sort by length and then alphanumerically.

-- View all the rows
SELECT * FROM documents
-- Order them by length and then alphanumerically
ORDER BY LENGTH(name), name
name
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt
file7.txt
file8.txt
file9.txt
file10.txt
file11.txt
file12.txt
file13.txt
file14.txt
file15.txt