Split single row value into columns

[wyconian]

Hey guys, hope you're all keeping busy in the cognosphere.

Think I have an interesting question for you, any help would be appreciated.

I have column called (TASKSUMMARY) in my source data that looks like this

:EMT:99:SOC:99:MFD:99:KYC:98:LCC:99:CBD:4:ISD:99:SSIR:99:CDT:99:DOC:99:

I want to be split it into different columns called SOC, MFD, KYC etc so that I will end up with something like

SOC   MFD   KYC   LCC   CBD
99   99   98   99   4

So split one column into multiple columns

I'm using DM8.4, the best solution I've come up with so far is to substr the column which works OK but seems a bit clunky and not very nice.

I'm using something like this
REPLACE(SUBSTR(TASKSUMMARY, INSTR(TASKSUMMARY,'SOC')+4,2),':','') SOC,
    REPLACE(SUBSTR(TASKSUMMARY, INSTR(TASKSUMMARY,'MFD')+4,2),':','') MFD,
    REPLACE(SUBSTR(TASKSUMMARY, INSTR(TASKSUMMARY,'KYC')+4,2),':','') KYC,
    REPLACE(SUBSTR(TASKSUMMARY, INSTR(TASKSUMMARY,'LCC')+4,2),':','') LCC,
    REPLACE(SUBSTR(TASKSUMMARY, INSTR(TASKSUMMARY,'CBD')+4,2),':','') CBD,
    REPLACE(SUBSTR(TASKSUMMARY, INSTR(TASKSUMMARY,'ISD')+4,2),':','') ISD,


Anyone got a better idea on how to do this?

As always thanks for the help, keep the faith

Wyconian

[S2000]

That's probably how I would do it.

If the EMT, SOC codes were variable you might need to try some sort of stored iterative proc that counts the number of ':' and repeats that many times picking up the next ':' each time.
But, that would be more of a SQL thing than a DM thing.

Or you could use derivations.
Each derivation being named SOC, MFD etc.
The code would be similar though.
Subtirng on the few character after the location of the code in the string.

[wyconian]

Thanks for the suggestion :)