RE: Another SQL question (yes, I know, I'm getting annoying; at least this is a different project)

This was some good practice for recursive common table expressions (RCTE)

*SQL Script*
create table teslib.newtable (infield char(20));

insert into teslib.newtable values('foo bar boz baz');

select * from teslib.newtable;

with
rownumbers as (select row_number() over() rowno, infield from teslib.newtable),
fields (level, rowno, fieldchar, pos) as
(select 1,
rowno,
substring(infield, 1, position(infield, ' ')),
position(infield, ' ')
from rownumbers
union all
select o.level + 1,
o.rowno,
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*

*Results*
LEVEL ROWNO FIELDCHAR POS
2 1 bar 8
4 1 baz 16
3 1 boz 12
1 1 foo 4
*END Results*

I use this page as my reference for all things CTE related that I forget
http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_rcte_olap.pdf

-Tom Stieger
IT Manager
California Fine Wire



-----Original Message-----
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?

--
JHHL
--
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.