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

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.