Create a Delimitted String from a Query in Db2

Create a delimitted string from a query in DB2

I'm trying to do this in OLEDB and from what I understand you can't do this because you can't do anything fancy in SQL for OLEDB like declare variables or create a table. So I guess there is no way.

How to split a string value based on a delimiter in DB2

This is what i tried and it fetched me effective result. Hence sharing with all.

select column_name, substr(column_name,1,locate('-',column_name)-1), 
substr(column_name,locate('-',column_name)+1,
length(substr(column_name,locate('-',column_name)+1))) from 
table_name where column_name is not null and column_name!='' 
and column_name like '%-%'

Select a portion of a comma delimited string in DB2/DB2400

I am answering my own question now. It is impossible to do this with the built in functions within AS400

You have to create an UDF of Oracle's INSTR

Enter this within STRSQL it will create a new function called INSTRB

CREATE FUNCTION INSTRB (C1 VarChar(4000), C2 VarChar(4000), N integer, M integer)
 RETURNS Integer
 SPECIFIC INSTRBOracleBase
 LANGUAGE SQL
 CONTAINS SQL
 NO EXTERNAL ACTION
 DETERMINISTIC
BEGIN ATOMIC
DECLARE Pos, R, C2L Integer;

SET C2L = LENGTH(C2);

IF N > 0 THEN
   SET (Pos, R) = (N, 0);
   WHILE R < M AND Pos > 0 DO
      SET Pos = LOCATE(C2,C1,Pos);
         IF Pos > 0 THEN
         SET (Pos, R) = (Pos + 1, R + 1);
      END IF;
   END WHILE;

   RETURN (Pos - 1)*(1-SIGN(M-R));
ELSE
   SET (Pos, R) = (LENGTH(C1)+N, 0);
   WHILE R < M AND Pos > 0 DO
      IF SUBSTR(C1,Pos,C2L) = C2 THEN
         SET R = R + 1;
      END IF;
      SET Pos = Pos - 1;
   END WHILE;

   RETURN (Pos + 1)*(1-SIGN(M-R));
END IF;

END

Then to select the nth delimited value within a comma delimited string... in this case the 14th

use this query utilizing the new function

SELECT SUBSTRING(C,INSTRB(C,',',1,13)+1,INSTRB(C,',',1,14)-INSTRB(C,',',1,13)-1) FROM TABLE

How to extract a value from a delimited string in Db2

Try this for table BILL and its column COL1 with data.

SELECT 
  COL1
-- since 9.7
, xmlcast(xmlquery('fn:tokenize($s, "\|")[3]' passing BILL.COL1 as "s") as varchar(20)) as one
-- since 11.1
, REGEXP_SUBSTR(BILL.COL1 || '|', '([^\|]*)\|', 1, 3, '', 1) as two
FROM 
(VALUES '0410|M|PAXG|20181114', '0410|M||20181114') BILL (COL1)
--BILL
;

Passing comma separated vlaues to DB2 for IN Clause

I hope this helps guys. I managed to fix it by changing my where clause to

    WHERE
            A . EMPSFT = 'Y'
            AND A . EMUKCD IN (
                SELECT regexp_substr(ADDRESSCODE,
                '[^,]+',
                1,
                LEVEL)
            FROM
                SYSIBM.SYSDUMMY1
            CONNECT BY
                regexp_substr(ADDRESSCODE,
                '[^,]+',
                1,
                LEVEL) IS NOT NULL)

Basically you need to do something like this

select regexp_substr('SMITH,ALLEN,WARD,JONES,sandhya, abe','[^,]+', 1, level) from SYSIBM.SYSDUMMY1
  connect by regexp_substr('SMITH,ALLEN,WARD,JONES, sandhya, abe', '[^,]+', 1, level) is not null;

I got this from the following link
Oracle link but modified it to suit DB2

Split a VARCHAR in DB2 to retrieve a value inside

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;

---

Related Topics