Split a VARCHAR in DB2 to retrieve a value inside

db2, split, sql, string

Solution

CREATE FUNCTION split(pos INT, delimeter CHAR, string VARCHAR(255))
LANGUAGE SQL
RETURNS VARCHAR(255)
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
    DECLARE x INT;
    DECLARE s INT;
    DECLARE e INT;

    SET x = 0;
    SET s = 0;
    SET e = 0;

    WHILE (x < pos) DO
        SET s = locate(delimeter, string, s + 1);
        IF s = 0 THEN
            RETURN NULL;
        END IF;
        SET x = x + 1;
    END WHILE;

    SET e = locate(delimeter, string, s + 1);
    IF s >= e THEN
        SET e = LENGTH(string) + 1;
    END IF;
    RETURN SUBSTR(string, s + 1, e - s -1);
END!

Usage:

SELECT split(3,'$',col) from mytable; -- or
SELECT split(0,'-', 'first-second-third') from sysibm.sysdummy1;
SELECT split(0,'-', 'returns this') from sysibm.sysdummy1;
SELECT split(1,'-', 'returns null') from sysibm.sysdummy1;

Problem

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 : - A DB2 build-in string function - An embeddable statement such as `SUBSTR(COL, POSSTR(COL)+1)...` - An user defined function that behaves like `SPLIT` Precision : Yes, I do know that it's not a good idea to have such columns...

Original source