If you try to use function TO_NUMBER in SQL/400 you will get "Token TO_NUMBER
was not valid". I didn't find way to to convert a string to a numeric in
SQL/400 so I don't use SQL/400 for that. If someone know way it is interesting.
From: Jromeh@aol.com [SMTP:Jromeh@aol.com]
Sent: 31 yiaa?y 1998 a. 19:39
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
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
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
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.
A related, ugly problem:
Extracting a packed or signed value embedded in a large character field
by a HLL program using a data structure. Can get a character string
representing the number out, but with (too) many function calls, could be
possible with one. Then apply TO_NUMBER, and your vendor package with the
"interesting" database is fully queryable. Should it be this hard?
MAP_PACKED and MAP_SIGNED functions sure would help here.
P.S. Recently received a post saying buds are out on trees, it's spring
already in NOLA!
P.P.S. Desiring an advance listen to the varied sounds of New Orleans?
Point your RealPlayer to www.wwoz.org. Sounds great from up nawth. And
when you're there, check out 90.7.