Re: numeric sorting on a char field having a decimal

Posted in 2006

Double Echo — Thu, 02 Feb 2006 09:29:33 -0500 — source: Usenet: comp.databases.informix

bozon wrote: >>>>After having seen this problem go unsolved, and after all the people attempting to solve it, I thought I'd give you my .02. > > >>>I solved it on the first day with the mantissa, notmantissa procedures. > > >>Well, you and others are certainly better at SQL than me, I never was good at SQL. > > > SQL for Smarties by Joe Celko. He has many examples of very smart > people coming up with very great solutions. > Thanks, I have his book, but SQL is not my cup 'o tea. My brain doesn't like SQL or trust it beyond simple select statements. Not to suggest that Informix has a bad SQL compiler, or any other vendor, I just don't trust SQL as much as others do. Plus, coming from an artistic background ( or autistic I'm not sure ) my brain doesn't work well with math, sets, or any of that. But thanks anyway...

↪ replying to Double Echo

Nog — 3 Feb 2006 03:41:17 -0800 — source: Usenet: comp.databases.informix

another option, with no need for a stored proc is; SELECT trunc(CASE WHEN (substr(trim(MYCOL1),-2,1) = ".") THEN TRUNC(MYCOL1,0) || "." || LPAD(MOD(MYCOL1*10,10),2,0) ELSE (TRUNC(MYCOL1,0) || "." || MOD(MYCOL1*100,100)+0) END,2) AS VAL from MYTABLE order by 1