Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    25

    Answered: Convert records with comma separated values for multi record in the different row

    I have some value in a table. These are records of a table in multi row format.

    12, 14, 17,18,32
    14, 16, 19,22
    18,22,23,32,35,37,38
    31,35,36,39,41

    Output will be all distinct values in different rows(without any comma):
    12
    14
    16
    17
    18
    19
    22
    23
    31
    32
    35
    36
    37
    38
    39
    41
    Based on the above input can the output be derived writing a single query in db2.
    Last edited by pp8771; 04-03-17 at 04:03.

  2. Best Answer
    Posted by mark.bb

    "Try this:
    Code:
    create function regexp_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 <i>{string($id)}</i>' 
    passing 
      source as "s"
    , pattern as "p"
    columns 
      seq for ordinality
    , tok varchar(4000) path '.'
    ) t;
    
    select distinct int(r.tok)
    from table(values
      '12, 14, 17,18,32'
    , '14, 16, 19,22'
    , '18,22,23,32,35,37,38'
    , '31,35,36,39,41'
    ) t(s)
    , table(regexp_tokenize(t.s, ',')) r;
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Try this:
    Code:
    create function regexp_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 <i>{string($id)}</i>' 
    passing 
      source as "s"
    , pattern as "p"
    columns 
      seq for ordinality
    , tok varchar(4000) path '.'
    ) t;
    
    select distinct int(r.tok)
    from table(values
      '12, 14, 17,18,32'
    , '14, 16, 19,22'
    , '18,22,23,32,35,37,38'
    , '31,35,36,39,41'
    ) t(s)
    , table(regexp_tokenize(t.s, ',')) r;
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •