0
New Here
,
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/td-p/501952
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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.
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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
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.
If you can extract the values into a numeric field you don't need the padding.
Guide
,
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501953#M45681
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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.
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501954#M45682
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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.
(2) Pad shorter entries with a zero - making an alpha sort look like a numeric.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Photoshare
AUTHOR
New Here
,
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501955#M45683
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Guide
,
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501957#M45685
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
Photshare,
If you can extract the values into a numeric field you don't need the padding.
If you can extract the values into a numeric field you don't need the padding.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Guide
,
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501956#M45684
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
LEGEND
,
LATEST
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501960#M45688
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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
easily done with the help of phpMyAdmin.
---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Photoshare
AUTHOR
New Here
,
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501958#M45686
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Guide
,
/t5/coldfusion-discussions/need-help-sorting-alphanumeric-list-with-1-coming-before-10/m-p/501959#M45687
Dec 04, 2007
Dec 04, 2007
Copy link to clipboard
Copied
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;
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;
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

