How to sort text with numbers with sql?

I am a new coder, and I am trying to figure out how to sort a field that may contain both letters and numbers, and have it appear in the correct numerical order.

The current sort syntax I have been using is:

ORDER BY CAST(minis.number AS UNSIGNED), minis.number

This has worked fine for this data:

1
5
10
11a
11b

However, now I have data with text in the prefix:

D&DC1
D&DC2
D&DC11

The above will sort as:

D&DC1
D&DC11
D&DC2

I do not want it to appear in this order – I want it to appear in the order above it.

Could someone please recommend a method of sorting that will work regardless of whether text is a prefix or a suffix.

Performing data manipulation like this in every query is not efficient. It would be better if you split the values apart and stored each part in a separate column, with an appropriate string/integer data type. You can then directly order the values as expected.

1 Like
SELECT
	columnName 
FROM
	yourtable 
ORDER BY
	LENGTH( columnName ),
	columnName;

What you’re describing is called natural sorting and can be achieved in MySQL by doing ORDER BY columnName+0.

So for your query it would become ORDER BY minis.number+0

See https://chrisjean.com/mysql-natural-sort-order-by-on-non-numeric-field-type/

UPDATE: This might not work with leading characters. In that case you should the solution from @benanamen above

If there are complications with the datebase query then perhaps using a PHP array function may help:

https://www.php.net/manual/en/ref.array.php

It’s probably going to be easier to handle at the receiving end than the database end; because as you have learned, it’s a case of neverending “Well IF this is true, then this will work, but if not…”.

benanamen’s query will work, as long as your prefixes are all the same length; it would sort “AAAA1” as coming after “B2”.

As mabismad says, it would be better to split the prefixes from the numeric sortable values.

@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

Nothing personal as it’s probably only me.

I would rather see an ALTER query that fixes the problem by adding a column than a query that “works”. Using database functions, or having code in the processing language work harder is something I guess, but I can’t help but think that fixing the problem closer to it’s source would be preferable.

OP, what is the nature of this data that it is so mixed with letters, numbers, and symbols?

Thank you all for the options. I will test them out to see how they work for my data.

@benanamen I host a fan-based pre-painted gaming miniatures website which is comprised of sets of collectible miniatures. Most of the miniatures simply have numbers that either range from 1 to 50 or 001 to 050. Sometimes they add in “variants” of a miniature, so they number it with a proceeding “a” or “b” (i.e. 24a). There are also some rare few promotional miniatures which have a letter prefix before the number, such as the D&DC example numbers above.

My hope is to come up with a way to sort these at the time the query is run, provided that it doesn’t significantly impact the query processing time.

I use the miniature number as the default sort order for the miniatures in each set.

I have used the code that benanamen suggested and its working great. Thanks again for all the help everyone.