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

[CRPence <CRPbottle@xxxxxxxxx>]

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