Friday, May 30, 2014

DB2 comma separated ids

CREATE OR REPLACE FUNCTION FN_TOKENIZE" (source clob(2M), pattern varchar(128))
    returns table (seq int, tok varchar(4000))
    contains sql  deterministic  no external action
return select seq, tok
from xmltable(
       'for $id in tokenize($s, $p) return {string($id)}'
            passing  source as "s", pattern as "p"  columns   seq for ordinality
      ,tok varchar(4000) path '.'
     ) t


SELECT * FROM TABLE WHERE ID IN (select INT(TOK) from table(fn_tokenize(YOUR_IDS, ',')))

No comments:

Post a Comment