Re: Query conversion

[dhandy@xxxxxxxxxxx (Douglas Handy)]

Bill/Frank/Jim,

>I am building/reformatting a database using Query/400. I would like to
>convert a character field to a numeric value. I can use DIGITS to go the
>other way, but is there a way to go this direction? 

I only know of one kludge, but it only handles character fields up to
6 digits long AND it must have leading zeros.

The trick is to use the MICROSECOND( timestamp ) function to extract
the numeric value of the last 6 bytes in a 26-byte timestamp.  The
idea is to concatenate a constant for the date/time portion with the
character field to create a timestamp, then let MICROSECOND( ) return
the numeric value of the portion you concatenated.

For example, if you have a character field containing 5 digits:

  NUM = MICROSECOND( '0001-01-01-00.00.00.0' || field )

or if you have a 3-digit character field:

  NUM = MICROSECOND( '0001-01-01-00.00.00.000' || field )

This has limited application, because you must know the number of
bytes and it must have leading zeros.  If it has leading blanks, the
timestamp expression becomes invalid and it returns 0.  If it has
trailing blanks, the blanks are treated as zeros.  EG, in the first
example above if the field contained '123  ' then NUM = 12300.

Like I said, it is a kludge with limited application.  But it does
help in a few cases.

Doug
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---