Re: Sorting numeric data stored as char (numeric vs. lexical)

Posted in 1999

Mark D. Stock — Mon, 27 Sep 1999 22:43:31 +0100 — source: Informix-list mailing list archive (1991-1998)

VP wrote: > > Sorting numeric data stored as char (numeric vs. lexical) > > I have numeric data stored in a char-column. > The data is NOT zero-padded. > > The question is, how do I retrieve the data in > sorted order (actually, I just want the LAST record) > > As you can imagine, the problem is that > numerically, 100 > 99, but lexically 99 > 100 > > I have the option of zero-padding all the data Don't bother, let Informix convert it for you: SELECT TRUNC(my_char+0) my_num FROM my_table WHERE ... ORDER BY 1 DESC Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock http://www.informix.com |//////// /| | mailto:mdstock@mydas.freeserve.co.uk |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+