Re: SQL string conversion

[Dave Shaw <dshaw1@xxxxxxxxxxx>]

-----Original Message-----
From: Jromeh@aol.com <Jromeh@aol.com>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Saturday, January 31, 1998 2:15 PM
Subject: Re: SQL string conversion


>Mark Lazarus asked...
>
>> Is there a way to convert a string to a numeric in SQL/400?  I have a
file
>>that is being uploaded from a PC.  The initial DB that it is being
uploaded
>>to has a single field.  The area in the record is being pre-screened to
>>make sure it is numeric.
>
>> What I wanted to do is something like this:  SELECT * WHERE VAL( SUBST(
>>DATA, 15, 7 ) ) > 100.  Or assign it to a numeric field an on update.  I
>>realize that there is no VAL function.  Is there an equivalent function or
>>workaround?
>
>The TO_NUMBER function does exactly this, it's covered on p84-85 of
>"Teach Yourself SQL in 21 Days" (Stevens, et al, SAMS Publishing, 2nd ed,
>1997). The book uses Access, Personal Oracle7, MS Query, and ODBC for
>examples.
>
>Never heard of such a thing in SQL/400, but get asked often and sure
>could use it at times, especially when dealing with data from other
>platforms.
>
>Is it there in ASC's SEQUEL, anyone? Seem to remember more control over
>some things like this, though it's almost 3 years now since using it.


ASC's Sequel doesn't support the TO_NUMBER function as such, but it does
provide the ability to do this task.  There is a ZONED function which
converts a (normally character) expression into an AS/400 zoned number,
which can then be converted to packed or integer or floating point.  It's
all very AS/400-eccentric and decidedly non-portable, of course.  It works
great, though - we've used it several times.

Dave Shaw, General Nutrition, Greenville, SC (just down the road from BMW -
Bubba Makes Wheels :)
The opinions expressed may not be my employer's unless I'm sufficiently
persuasive...


root
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@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
+---