Mysql Natural Sort Mixed Alphanumeric – Numeric Alphanumeric First

SQL:

SELECT
stock
FROM
cards
ORDER BY
IF(stock REGEXP ‘^[A-Z]’,
CONCAT(
LEFT(stock, 1),
LPAD(SUBSTRING(stock, 2), 20, ‘0’)),
CONCAT(
‘@’,
LPAD(stock, 20, ‘0’)))

SQL SCHEMA:

— —————————-
— Table structure for cards
— —————————-
DROP TABLE IF EXISTS cards;
CREATE TABLE cards (
stock varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— —————————-
— Records of cards
— —————————-
INSERT INTO cards VALUES (‘I1’);
INSERT INTO cards VALUES (‘I2’);
INSERT INTO cards VALUES (‘I3’);
INSERT INTO cards VALUES (‘I4’);
INSERT INTO cards VALUES (‘W1’);
INSERT INTO cards VALUES (‘W2’);
INSERT INTO cards VALUES (‘W3’);
INSERT INTO cards VALUES (‘W10’);
INSERT INTO cards VALUES (‘W11’);
INSERT INTO cards VALUES (‘I12’);
INSERT INTO cards VALUES (‘I13’);
INSERT INTO cards VALUES (‘I14’);
INSERT INTO cards VALUES (‘I15’);
INSERT INTO cards VALUES (‘I20’);
INSERT INTO cards VALUES (‘IC1’);
INSERT INTO cards VALUES (‘IC2’);
INSERT INTO cards VALUES (‘IC3’);

“This transforms a number like ‘123’ to ‘@00000000000000000123’ and an alphanumeric like ‘A22’ to ‘A00000000000000000022’. These strings will sort in the fashion you want.” — NovaDenizen

SOURCE: https://www.experts-exchange.com/questions/22092935/Ordering-mixed-numeric-alphanumeric-list.html

Leave a Reply

Your email address will not be published. Required fields are marked *