Monrow - answer as promised

[Thargy]

Monrow,

I hope you find this forum useful. As promised I have a solution for you below.

Run the script below to build and populate a table with sample data.

CREATE TABLE MONROW
(
A    VARCHAR2(5),
B    SMALLINT,
C    VARCHAR2(100)
);

INSERT INTO MONROW VALUES ('code1',1,'ZE');
INSERT INTO MONROW VALUES ('code2',2,'ER:GP');
INSERT INTO MONROW VALUES ('code3',3,'SP:NS:LK');
COMMIT;

Once data has been loaded, run the following query

WITH DATA AS
(SELECT     LEVEL L
      FROM DUAL
CONNECT BY LEVEL <
                  (SELECT MAX (LENGTH (c) - LENGTH (REPLACE (c, ':', ''))) + 2
                     FROM MONROW))
SELECT A,B,
SUBSTR(':'||C||':',INSTR(':'||C||':',':',1,L)+1, INSTR(':'||C||':',':',1,L+1)- INSTR(':'||C||':',':',1,L)-1) INITIALS
  FROM MONROW,DATA
 WHERE SUBSTR(':'||C||':',INSTR(':'||C||':',':',1,L)+1, INSTR(':'||C||':',':',1,L+1)- INSTR(':'||C||':',':',1,L)-1) IS NOT NULL
 ORDER BY A,L

The output from this query is

A     B INITIALS
code1 1 ZE
code2 2 ER
code2 2 GP
code3 3 SP
code3 3 NS
code3 3 LK

This caters for any number of colon-delimited entries.

Regards

T

 

[SantaMufasa]

John,

So that subsequent readers can benefit from your posting, could you indicate what thread this relates to?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Dave,

thread259-1497340 was the cause of the post. I responded to an apparently desperate post in the oracle developer forum, and advised that the main oracle forums were best for the problem posted.



Regards

T