Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Need Help Sorting Alphanumeric list - with 1 coming before 10

New Here ,
Dec 04, 2007 Dec 04, 2007
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.
TOPICS
Advanced techniques
810
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Guide , Dec 04, 2007 Dec 04, 2007
Photshare,

If you can extract the values into a numeric field you don't need the padding.
Translate
Guide ,
Dec 04, 2007 Dec 04, 2007
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Dec 04, 2007 Dec 04, 2007
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 04, 2007 Dec 04, 2007
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Dec 04, 2007 Dec 04, 2007
Photshare,

If you can extract the values into a numeric field you don't need the padding.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Dec 04, 2007 Dec 04, 2007
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.



Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 04, 2007 Dec 04, 2007
LATEST
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
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 04, 2007 Dec 04, 2007
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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Dec 04, 2007 Dec 04, 2007
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;
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources