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

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Dennis,

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.

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Dec 9, 2009 at 10:23 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx>wrote:

> Hi, Folks:
>
> I have a column (fooCol) in a table (footab) that contains string data
> looking like the following:
> '961  6'
> '2809  6'
> '67  6'
> '66  6'
> '2879  6'
> '2874  6'
>
> (No, the second set is not always 6.  This is a column that was loaded
> incorrectly due to lack of a comma at the source end, and I'm trying to
> find
> an efficient way to live with it (longer story)).
>
> I have need of *efficiently* returning the first set of digits and the
> second set of digits as separate numeric values, so that they may be used
> in
> an UPDATE or INSERT statement or embedded SQL where the result is numeric.
>
> Any ideas?  I thought of using something like select left(foocol, instr('
> ',
> foocol)) (which is mySQL syntax) for the "left number", but I couldn't find
> an instr equivalent and, even if I did it'd still be a string value.  The
> second set would probably be a no-brainer after landing on a solution for
> the first.
>
> Thanks for any pointers.
>
> Dennis Lovelady
> http://www.linkedin.com/in/dennislovelady
> --
> "All progress is based upon a universal innate desire on the part of every
> organism to live beyond its income."
>        -- Samuel Butler
>
> --
> 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.