• Subject: RE: SQL string conversion
  • From: Andrew Sereda <avs@xxxxxxxx>
  • Date: Sun, 1 Feb 1998 14:50:20 +0200

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.

Andrew Sereda

-----Original Message-----
From:   Jromeh@aol.com [SMTP:Jromeh@aol.com]
Sent:   31 yiaa?y 1998 a. 19:39
To:     MIDRANGE-L@midrange.com
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.


This thread ...

Return to Archive home page | Return to MIDRANGE.COM home page