Need Help Sorting Alphanumeric list - with 1 coming before 10

[Photoshare]

I am querying a table of categories and while the category field is a text field some of the data begins with numbers. What I have never been able to figure out how to do is sort categories so that smaller numbers appear before larger ones. See example:
4 Megapixel
5 Megapixel
6 Megapixel
10 Megapixel
11 Megapixel
21 Megapixel
etc.

When I sort this list using listsort the list appears as follows:

10 Megapixel
12 Megapixel
21 Megapixel
4 Megapixel
5 Megapixel
6 Megapixel

i was wondering (hoping) that someone here would know how I can sort these items so that they appear like in first list.

Thank you.

[cf_dev2]

The best option is to extract the number and store it in a separate numeric field. Barring that, you could use your db's string functions to extract the numeric portion. Convert it to a number and sort on it.

If by chance you're a QoQ, I don't know if its possible without looping through each row and using CF's string functions.

[Guest]

Your first list is an example of a numeric sort, the 2nd an example of a textual sort. You have two choices: (1) separate the numeric and alpha data into two fields, or
(2) Pad shorter entries with a zero - making an alpha sort look like a numeric.

[Photoshare]

Integer and Numeric (Decimal) fields in MySQL don't seem to allow a leading "0." I think I've tried this in the past to no avail.

[cf_dev2 · Accepted solution]

Photshare,

If you can extract the values into a numeric field you don't need the padding.

[cf_dev2]

quote:

---

Originally posted by: coffeedrinker56
(2) Pad shorter entries with a zero - making an alpha sort look like a numeric.

---

If the max value (ie length of the number) is know or you can pick a large enough value. Separating the numeric portion of the value is usually the simpler option, but it depends.

[Newsgroup_User]

they actually do, but you have to specify your field as "zerofill".
easily done with the help of phpMyAdmin.

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[Photoshare]

I guess I could try to use a regex statement to look for the characters before the first space " " in the category name then check to see it's numeric.

[cf_dev2]

Yes. I was going to suggest PATINDEX but I don't think mySQL has that function. Something like this might work to extract the numeric values in one pass.

Caveat: It only works when the numbers are at the beginning of the string AND followed by a space.

UPDATE YourTable
SET TheNumericColumn =
CAST(SUBSTRING(TheStringColumn, 1, INSTR(TheStringColumn, ' ')-1) AS UNSIGNED)
WHERE TheStringColumn RLIKE '(^[0-9]+ )' = 1;