Split a VARCHAR in DB2 to retrieve a value inside

Question

I have a VARCHAR column that contains 5 informations (2 CHAR(3) and 3 TIMESTAMP) separated with '$'.

CREATE TABLE MYTABLE (
  COL VARCHAR(256) NOT NULL
);

INSERT INTO MYTABLE 
VALUES
    ( 'AAA$000$2009-10-10 10:50:00$null$null$null' ),
    ( 'AAB$020$2007-04-10 10:50:00$null$null$null' ),
    ( 'AAC$780$null$2007-04-10 10:50:00$2009-04-10 10:50:00$null' )
;

I would like to extract the 4th field ...

'AAA$000$2009-10-10 10:50:00$null$null$null'
                             ^^^^ this field

... to have something like

SELECT SPLIT(COL, '$', 4) FROM MYTABLE

1
-----
'null'
'null'
'2009-04-10 10:50:00'

I'm searching, in that order :

1. A DB2 build-in string function
2. An embeddable statement such as SUBSTR(COL, POSSTR(COL)+1)...
3. An user defined function that behaves like SPLIT

Precision : Yes, I do know that it's not a good idea to have such columns...

Answer 1

I am sure there is a better way to write this, but here is 1 (SQL) solution for the simple case given. It could be rewritten as a stored procedure to look for any arbitrary string. There may also be some 3rd party tools/extensions to help out w/ the split you want...

select
locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1) as poss3rdDollarSign, -- position of 3rd dollar sign
locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1) as poss4thDollarSign, -- position of 4th dollar sign
    (locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1)) - 
    (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) - 1  as stringLength,-- length of string between 3rd and 4th dollar sign
    substr(col, locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)  + 1, (locate('$', col, (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) + 1)) - 
    (locate('$', col, (locate('$',col, (locate('$',col) +1))) + 1)) - 1) as string
    from mytable