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

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