MySQL Natural Sorting with ORDER BY Clause: Examples

Examples
Sample Database

#1

CREATE TABLE IF NOT EXISTS items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);

#2

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');

#3

SELECT 
    item_no
FROM
    items
ORDER BY item_no;

#4

SELECT 
    CONCAT(prefix, suffix)
FROM
    items
ORDER BY prefix , suffix;

#5

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

#6

TRUNCATE TABLE items;

INSERT INTO items(item_no)
VALUES('A-1'),
      ('A-2'),
      ('A-3'),
      ('A-4'),
      ('A-5'),
      ('A-10'),
      ('A-11'),
      ('A-20'),
      ('A-30');

#7

SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;

Table List

customers

SELECT * FROM customers;

employees

SELECT * FROM employees;

offices

SELECT * FROM offices;

orderdetails

SELECT * FROM orderdetails;

orders

SELECT * FROM orders;

payments

SELECT * FROM payments;

productlines

SELECT * FROM productlines;

products

SELECT * FROM products;

Result