Hi,

I've searched on Dejanews for an answer to this, but it's one of those
hard to find things.  I have a sql query where I need to order the
contents of a database.  The data is numeric, but it's stored in a
string field.  Instead of 1, 2, 3  I get 1, 10, 11, 2, 20, etc.  

I found a way to use substr to first process records of one digit,
then records of two digits, and then of three, which worked fine for
some purposes.  Unfortunately, now I'm forced to get all of them back
in a single table and those tricks don't work.  What I'm missing is
the SQL language to convert a string to a number, or some way of
branching or conditional operators...

Thanks in advance,
Jim

On Thu, 29 Oct 1998 19:28:56 GMT, jimm...@worldnet.att.net (Jim Mack)
wrote:

Another way to do this -- without a menagerie of SUBSTRING functions -- is
to convert the column in the SELECT clause to a number (typically, SMALLINT
or INTEGER) and then use that converted value in the ORDER BY clause. For
your visual controls, simply make this ordering column invisible. Something
like this:

  SELECT CAST(Y.CharColumn AS SMALLINT), Y.*
  FROM YourTable Y
  ORDER BY 1

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham

THANKS!!!

I didn't find cast until I just looked for it now, and I also never
saw trim, upper, lower, or ||.  Have these been in there all along?
Oddly enough, I've known about extract for date fields, but this is
what I've been looking for!

Jim