How to sort text with numbers with sql?

[orclord1]

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.

[mabismad]

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.

[benanamen]

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

[rpkamp]

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

[John_Betong]

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

[m_hutley]

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.

[benanamen]

@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 |
+--------+

[Mittineague]

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.

[benanamen]

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

[orclord1]

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.

[orclord1]

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