This was some good practice for recursive common table expressions (RCTE)
create table teslib.newtable (infield char(20));
insert into teslib.newtable values('foo bar boz baz');
select * from teslib.newtable;
rownumbers as (select row_number() over() rowno, infield from teslib.newtable),
fields (level, rowno, fieldchar, pos) as
substring(infield, 1, position(infield, ' ')),
position(infield, ' ')
select o.level + 1,
substring(infield, o.pos+1, position(substring(infield, o.pos+1), ' ')),
position(substring(infield, o.pos+1), ' ')+o.pos
from rownumbers as next_layer
join fields o on o.rowno = next_layer.rowno)
cycle pos set cyclic_data to '1' default '0'
select * from fields where fieldchar <> '' order by fieldchar;
*end SQL script*
LEVEL ROWNO FIELDCHAR POS
2 1 bar 8
4 1 baz 16
3 1 boz 12
1 1 foo 4
I use this page as my reference for all things CTE related that I forget
California Fine Wire
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James Lampert
Sent: Friday, October 05, 2012 11:01 AM
To: Midrange Systems Technical Discussion
Subject: Another SQL question (yes, I know, I'm getting annoying; at least this is a different project)
Suppose we have a character field in a PF, that has several short "words" in it (representing multiple values in a multi-select list box), delimited by spaces.
e.g., "foo bar boz baz"
Is there a pure-SQL, Midrange-compatible, way to sort the "words" in the field (producing "bar baz boz foo" in the above example)?
Or to parse them into separate fields, without necessarily requiring them to all be the same length?
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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l