Splitting columns with iseries sql

Question

I have a table on iseries (V6R1) looking like this:

REF                           BOOKING             NAME
--------------------------------------------------------------
032 220 3564                  1354789             Peter Parker
att. Lois                     1156872             Clark Kent
checklist no. 1                864723             Matt Murdoch

I'm trying to split column REF into 3 new columns, the split on the space-character. The result of the split should be

REF1          REF2          REF3          BOOKING             NAME
---------------------------------------------------------------------------
032           220           3564          1354789             Peter Parker
att.          Lois                        1156872             Clark Kent
checklist     no.           1              864723             Matt Murdoch

I've been looking at POSSTR, CHARINDEX, SUBSTR but can't make it Work. Help will be appreciated.

Answer 1

There isn't an easy way to do this. The following works, but extending it past 3 columns would be really ugly.

If you have to do this often, consider building a UDF that returns a particular occurrence.

select ref                                                     
     , substr(ref                                              
             ,1                                                 
             ,locate(' ',ref,1)
             ) as ref1                           
     , substr(ref                                              
              ,locate(' ',ref,1) + 1                           
              ,locate(' ',ref, locate(' ',ref,1) + 1)         
               - locate(' ',ref,1)                             
             ) as ref2                                                 
     , substr(ref                                              
             , locate(' ',ref, locate(' ',ref,1) + 1) + 1     
             ) as ref3                                                 
from mytable