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

Modified for more varied Data

mysql> SELECT
data
FROM
mytable
ORDER BY
IF(data REGEXP '^[A-Z]',
CONCAT(
LEFT(data, 1),
LPAD(SUBSTRING(data, 3), 20, '0')),
CONCAT(
'@',
LPAD(data, 20, '0')
)),
LENGTH( data ),
data;
+———–+
| data |
+———–+
| AAAA1 |
| B2 |
| D&DC1 |
| D&DC2 |
| D&DC11 |
+———–+