434,880 Members | 2,292 Online
Need help? Post your question and get tips & solutions from a community of 434,880 IT Pros & Developers. It's quick & easy.

# SQL to Generate Record Combinations from input string

 P: n/a Hi people, Given I have a string record say "A+B+C+D+E", is it possible to parse this string using SQL in DB2 and return all of the possible combinations from the 5 elements to a min length of 3 by returning the following records ? "A+B+C+D+E" "A+B+C+D" "A+B+C" "A+C+D+E" "A+C+D" "A+B+D+E" "A+B+D" "A+B+C+E" "A+B+E" "B+C+D+E" "B+C+D" "C+D+E" I need somehow to do this on a daily basis and insert the returned records into a result table I can query later. Frankly I have no clue how I supposed to do this, so any help would be greatly appreciated. I'm using win2k server, DB2 8.2.2 ESE. Many thanks, Tim Nov 12 '05 #1
5 Replies

 P: n/a Doh ! Left out : "B+D+E" "B+C+E" "A+C+E" "A+D+E" Nov 12 '05 #2

 P: n/a This may be too complicated. More simplified way will be there. Anyway, this worked. ------------------------- Commands Entered ------------------------- WITH Given (str) AS ( VALUES 'A+B+C+D+E' ) , -- REMOVE 1st STRING Separator_pos AS ( SELECT n , INT(ROWNUMBER() OVER(ORDER BY n)) s FROM (SELECT n FROM Given g , TABLE (SELECT 10*a + b n FROM (VALUES 0,1,2,3,4,5,6,7,8,9) p(a) , (VALUES 0,1,2,3,4,5,6,7,8,9) q(b) WHERE 10*a + b BETWEEN 1 AND LENGTH(str) ) r WHERE SUBSTR(str,n,1) = '+' UNION ALL SELECT LENGTH(str)+1 AS n FROM Given ) s ) , Remove1 AS ( SELECT SUBSTR(INSERT('+'||str,n1+1,n2-n1,''),2,20) str , s2 AS sn FROM Given , (SELECT n, s FROM Separator_pos UNION ALL (VALUES (0, 0)) ) AS p(n1,s1) , Separator_pos AS q(n2,s2) WHERE s1 + 1 = s2 ) , -- REMOVE 2nd STRING Separator_pos2 AS ( SELECT sn , n , INT(ROWNUMBER() OVER(PARTITION BY sn ORDER BY n)) s FROM (SELECT sn, n FROM Remove1 g , TABLE (SELECT 10*a + b n FROM (VALUES 0,1,2,3,4,5,6,7,8,9) p(a) , (VALUES 0,1,2,3,4,5,6,7,8,9) q(b) WHERE 10*a + b BETWEEN 1 AND LENGTH(str) ) r WHERE SUBSTR(str,n,1) = '+' UNION ALL SELECT sn, LENGTH(str)+1 AS n FROM Remove1 ) s ) , Remove2 AS ( SELECT SUBSTR(INSERT('+'||str,n1+1,n2-n1,''),2,20) str FROM Remove1 , (SELECT sn, n, s FROM Separator_pos2 UNION ALL SELECT sn, 0, 0 FROM Separator_pos2 ) AS p(sn1,n1,s1) , Separator_pos2 AS q(sn2,n2,s2) WHERE sn1 = sn AND sn2 = sn AND s1 + 1 = s2 ) SELECT str FROM ( SELECT str FROM Given UNION SELECT str FROM Remove1 UNION SELECT str FROM Remove2 ) Q(str) ORDER BY RTRIM(str) || chr(255) ; -------------------------------------------------------------------- STR -------------------- A+B+C+D+E A+B+C+D A+B+C+E A+B+C A+B+D+E A+B+D A+B+E A+C+D+E A+C+D A+C+E A+D+E B+C+D+E B+C+D B+C+E B+D+E C+D+E 16 record(s) selected. Nov 12 '05 #3

 P: n/a Thats amazing, thank you so much Tonkuma, very much appreciated. Nov 12 '05 #4

 P: n/a CREATE TABLE Foobar (sillystring VARCHAR(12) NOT NULL PRIMARY KEY); INSERT INTO Foobar (sillystring) VALUES ('A+B+C+D+E'); CREATE TABLE Elements(item VARCHAR(3) NOT NULL PRIMARY KEY); INSERT INTO Elements VALUES ('A+'); INSERT INTO Elements VALUES ('B+'); INSERT INTO Elements VALUES ('C+'); INSERT INTO Elements VALUES ('D+'); INSERT INTO Elements VALUES ('+E'); Now a pure functional solution: SELECT sillystring -- fives FROM Foobar UNION -- fours SELECT REPLACE (REPLACE (F1.sillystring, E.item, ''), '++', '+') FROM Foobar AS F1 CROSS JOIN Elements AS E UNION SELECT REPLACE (REPLACE (REPLACE (F1.sillystring, E1.item, ''), E2.item, ''), '++', '+') FROM Foobar AS F1 CROSS JOIN Elements AS E1 CROSS JOIN Elements AS E2; Nov 12 '05 #5

 P: n/a A little shorter example. This use one less common table expression than previous version. And this remove two strinngs at once directly from given string. Then no duplicate rows will not be produced. So, UNION ALL are used in subquery of last query. WITH Given (str) AS ( VALUES 'A+B+C+D+E' ) -- Find separator positions , Separator_pos AS ( SELECT n , INT(ROWNUMBER() OVER(ORDER BY n)) s FROM (SELECT n FROM Given g , TABLE (SELECT 10*a + b n FROM (VALUES 0,1,2,3,4,5,6,7,8,9) p(a) , (VALUES 0,1,2,3,4,5,6,7,8,9) q(b) WHERE 10*a + b BETWEEN 1 AND LENGTH(str) ) r WHERE SUBSTR(str,n,1) = '+' UNION ALL SELECT LENGTH(str)+1 AS n FROM Given ) s ) -- REMOVE one STRING , Remove1 AS ( SELECT SUBSTR(INSERT('+'||str,n1+1,n2-n1,''),2,20) str FROM Given , (SELECT n, s FROM Separator_pos UNION ALL (VALUES (0, 0)) ) AS p1(n1,s1) , Separator_pos AS p2(n2,s2) WHERE s1 + 1 = s2 ) -- REMOVE two STRINGs , Remove2 AS ( SELECT SUBSTR(str,1,n1-1) || SUBSTR(str,n2+1-SIGN(s1),n3-n2-(1-SIGN(s1))*SIGN(s3-1)) || SUBSTR(str||' ',n4+1-SIGN(s3-1),LENGTH(str)-n4+SIGN(s3-1)) AS str FROM Given , (SELECT n, s FROM Separator_pos UNION ALL (VALUES (1, 0)) ) AS p1(n1,s1) , Separator_pos AS p2(n2,s2) , Separator_pos AS p3(n3,s3) , Separator_pos AS p4(n4,s4) WHERE s1 + 1 = s2 AND s2 <= s3 AND s3 + 1 = s4 ) SELECT str FROM ( SELECT str FROM Given UNION ALL SELECT str FROM Remove1 UNION ALL SELECT str FROM Remove2 ) Q(str) ORDER BY RTRIM(str) || chr(255) ; -------------------------------------------------------------------- STR ---------------------------- A+B+C+D+E A+B+C+D A+B+C+E A+B+C A+B+D+E A+B+D A+B+E A+C+D+E A+C+D A+C+E A+D+E B+C+D+E B+C+D B+C+E B+D+E C+D+E 16 record(s) selected. Nov 12 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.