I wrote a CFX a couple of years ago to solve this very problem (mixed alpha-numeric data that people want sorted in quasi-numeric order). C++, Windows only. Works in 4.5 but not tested in 5. Not appropriate for huge result sets.
http://devex.allaire.com/developer/gallery/info.cfm?ID=44C48F77-35E9-11D4-83 D700508B94F85A&method=Full > -----Original Message----- > From: Gonzo Rock [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, November 20, 2001 8:47 PM > To: CF-Talk > Subject: RE: Sorting Errors > > > Opps, my mistake in explaining..... > > ahh... some of the Lot#'s are... alphanumeric > > for example: > > 1 > 10-A > 1000 > 101-B1C2a > 201 > 21 > > and this should sort as... > > hummm... I'm not sure what I'm doing anymore... I have to > give this more thought.... > > 1 > 10-A > 21 > 101-B1C2a > 201 > 1000 > At 02:10 PM 11/21/01 +1300, you wrote: > >wouldn't it be better to have the LOT# field as int? > > > >-----Original Message----- > >From: Gonzo Rock [mailto:[EMAIL PROTECTED]] > >Sent: Wednesday, 21 November 2001 2:02 p.m. > >To: CF-Talk > >Subject: Sorting Errors > > > > > >I have a problem that I have a solution to but would like to > query the group > >to see if there is some more elegant solution. > > > >Simply Put, > > > >When using varchar(10) as a datatype, (for a LOT# field used in > >manufacturing), the typical number sequence sorts with > values like 1000 > >above something like 101. > > > >Example: > > > >1 > >10 > >1000 > >101 > >201 > >21 > > > >I just pad the left with blanks in the database so that it > sorts like this. > > > > 1 > > 10 > > 21 > > 101 > > 201 > > 1000 > > > >Is there some more elegant or more standard way that I > should be using? > > > >Thanks to anyone who chimes in. > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
