Function DB2 Table Function

Question

Does someone knows how can I get that result bellow in DB2?

Query

Example : 

    SELECT * FROM TABLE(FunctionName('1||2||3',||)) ;

First one are parameters with values and second one is delimiter.

Result:

    Column

    1
    2
    3

Values that I put on that query are based on parameters and delimiters.

As I said above, rows is break by Pipe. I dont have huge background on PL/SQL.

Im using DB2 v11.1

Do you mean ` SELECT * FROM TABLE(FunctionName('1||2||3','||')) ;` — mao, Dec 27 '17 at 13:39
Possible duplicate of https://stackoverflow.com/questions/18961996/how-to-split-a-string-value-based-on-a-delimiter-in-db2 — mao, Dec 27 '17 at 13:40

score 1 · Answer 1 · answered Dec 27 '17 at 15:55

So ive been facing that problem , and i found a solution for it :

CREATE OR REPLACE FUNCTION Test(
Data_1 CLOB(1M), Delimtator VARCHAR(12))
RETURNS TABLE (
FieldData varchar(2048))
LANGUAGE SQL
BEGIN
    DECLARE dInic INTEGER DEFAULT 1 ;
    DECLARE dFim  INTEGER DEFAULT 0 ;
    DECLARE Rowid1 INTEGER DEFAULT 0;
    DECLARE Campo VARCHAR(2048);

    IF Data_1 IS NULL THEN
        RETURN;
    END IF;

    SET dFim=LOCATE(Delimtator,Data_1);

    WHILE dFim>0 DO
     SET Campo=SUBSTRING(Data_1,dInic,dFim-dInic);
        PIPE (Campo);
        SET dInic=dFim+LENGTH(Delimtator);
        SET dFim=LOCATE(Delimtator,Data_1,dFim+LENGTH(Delimtator));
    END WHILE;

    SET Campo=SUBSTRING(Data_1, dInic,LENGTH(Data_1)-dInic+1);
    PIPE (Campo);
    RETURN;
END@

I like that you did this. Could you comment the code. – danny117 Dec 29 '17 at 17:09 — danny117, Dec 29 '17 at 17:09

Function DB2 Table Function

1 Answers1