How to sort text with numbers with sql?

Databases
9

@m_hutley does have a point. If your data formatting is rather varied, this will work for you.

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 |
+--------+
1 Like