Need Help Sorting Alphanumeric list - with 1 coming before 10

Forum|Forum|18 years ago
December 4, 2007
4 replies
961 views

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.

Advanced techniques

This topic has been closed for replies.

Correct answer cf_dev2

Photshare,

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

C

cf_dev2

Inspiring

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;

P

PhotoshareAuthor

Known Participant

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.

A

Anonymous

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.

P

PhotoshareAuthor

Known Participant

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.

C

cf_dev2Correct answer

Inspiring

Photshare,

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

C

cf_dev2

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded