Re: String to integer in SQL similar to atoi()

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Hi,

Chuck is right, my syntax is wrong .... (So much for trying to do things
from memory)

I think you statement should be something like this:
*********
Select
   CAST(
     SUBSTR(foocol, 1, POSITION(' ' in foocol) - 1)
      AS Numeric(12, 0)
       ) as N1,

   CAST(
     SUBSTR(foocol, POSITION(' ' in foocol) + 1)
      AS Numeric(12, 0)
       ) as N2
  FROM footab
*********

Again, I'm at home and not able to check for a proper syntax. If you wish, I
can check it tomorrow at the office (we are running V5R3).

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Thu, Dec 10, 2009 at 6:46 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

> Dennis Lovelady wrote:
> > Thank you, Luis!  Yes, I should have thought of CAST.
> >
> > sjl wrote:
> > > Wrap the SUBSTR's with a CAST to convert them to numeric,
> > > and I believe you've got the problem solved.
> > > Luis wrote:
> > > > I think you could do something like this:
> > > > *********
> > > >  Select
> > > >    Substr(foocol, 1, position(' ', foocol) - 1) as N1,
> > > >    Substr(foocol, position(' ', foocol) + 1) as N2
> > > >  FROM footab
> > > > *********
> > > > Not very sure about the exact syntax, as I'm not near a
> > > > computer right now.
> >
> > Having said that, on V5R3 the statement's choking on the comma
> > within position( , ). I suspect that position is not a function
> > defined in V5R3.
> > Still in search for that pesky function like %SCAN in RPG or
> > INSPECT in COBOL or C's strstr() ... (Again, though I did have
> > the parms backward, the mySQL equivalent is instr().)
>
>    A comma is incorrect syntax for POSITION; change the function to
> either LOCATE or POSSTR, or replace the comma with the token IN.
>
>   Searching InfoCenter on a known SQL built-in function name in my
> experience is best effected with: sql scalar position
>   The search results should list "Scalar functions" and perhaps
> "Built-in functions", from which LOCATE, POSITION, and POSSTR should
> be found, even in v5r3.  The syntax for each:
>
>  >>-LOCATE--(--search-string--,--source-string--+----------+--)-><
>                                                '-,--start-'
>
>  >>-+-POSITION--(--search-string--IN--source-string--)-+--------><
>
>  >>-+-POSSTR--(--source-string--,--search-string--)----+--------><
>
> Regards, Chuck
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.