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 5 2389
Doh !
Left out :
"B+D+E"
"B+C+E"
"A+C+E"
"A+D+E"
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.
Thats amazing, thank you so much Tonkuma, very much appreciated.
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;
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mamuninfo |
last post by:
Hello,
Have any function in the DB2 database that can generate unique id
for each string like oracle, mysql,sybase,sqlserver database.
In mysql:-
select md5(concat_ws("Row name")) from...
|
by: hikums |
last post by:
I am posting this here, just in case anyone may need this.
Step 1:
CREATE SEQUENCE ID_SEQ
START WITH 1050000
INCREMENT BY 1
MAXVALUE 9999999
NO CYCLE
NO CACHE
ORDER
|
by: mark |
last post by:
Access2000
How do I write a query that combines the CTC field from each record below into
one record? I need to concatenate the CTC field with a separator, like below:
...
|
by: Mark |
last post by:
I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the...
|
by: SalimShahzad |
last post by:
Dear Gurus,
i had written following codes to auto generate the next claim no
Private Const strC = "GCT/02/J/"
Private Sub Command1_Click()
Dim stra, stre As String
Dim intb, intd As Integer...
|
by: Tony Ciconte |
last post by:
Does anyone know of or have any VBA code or similar logic that can
help distinguish similar first/last name combinations? For example, we
would like to prompt the user of a possible match when any...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: The Frog |
last post by:
Hello everyone,
I am trying to find way of writing an SQL query that can produce
missing record combinations across a many to many type setup in
Access.
The three tables used are as follows:...
|
by: Geoff Cox |
last post by:
Hello,
Is it possible to use php to generate different menus for users who
have access to files in different folders?
For example, user Fred might be able to access files in folders A, B
and...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |