ibm midrange - Splitting columns with iseries sql -

ibm midrange - Splitting columns with iseries sql -

i have table on iseries (v6r1) looking this:

ref booking name -------------------------------------------------------------- 032 220 3564 1354789 peter parker att. lois 1156872 clark kent checklist no. 1 864723 matt murdoch

i'm trying split column ref 3 new columns, split on space-character. result of 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 @ posstr, charindex, substr can't create work. help appreciated.

there isn't easy way this. next works, extending past 3 columns ugly.

if have often, consider building udf returns particular occurrence.

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

sql ibm-midrange