Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    10

    Unanswered: Parsing String using built-in function

    All,

    Please let me know about sql standard functions(not the aggregate functions) available in DB2 database?
    Also, which function is used for parsing one string(having comma(,)separated values.(Note that it may possible that the column is having only one value without comma.
    e.g Col1
    1,2,21,3
    5
    6,9

    Hope the question is clear to all.

    Thank you in advance.

    ###############
    Regards
    Baseet Ahmed
    ###############

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What do you want to achieve?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I don't know the function which can be used for parsing,
    the way to parse string is repeatedly asked on some forums.

    I know two basic ways:
    1) Use recursive query.
    2) Join with integer table(n).

    1) Use recursive query.
    Code:
    WITH
     Test_Data(Col1) AS (
    VALUES
     '1,2,21,3'
    ,'5'
    ,'6,9'
    )
    /* End of Test_Data */
    ,Repeat(k, element, rest, col1) AS (
    SELECT 0
         , CAST('' AS VARCHAR(3))
         , col1||', '
         , col1
      FROM Test_Data
    /* */ UNION ALL /* */
    SELECT k
         , SUBSTR(rest, 1, p-1)
         , SUBSTR(rest, p+1)
         , col1
      FROM (SELECT k + 1 AS k
                 , POSSTR(rest, ',') AS p
                 , rest
                 , col1
              FROM Repeat
             WHERE k < 10000
               AND LENGTH(rest) > 1
           ) S
    )
    SELECT col1
         , k
         , element
      FROM Repeat
     WHERE k > 0
     ORDER BY
           col1
         , k;
    ------------------------------------------------------------------------------
    
    COL1     K           ELEMENT   
    -------- ----------- ----------
    1,2,21,3           1 1         
    1,2,21,3           2 2         
    1,2,21,3           3 21        
    1,2,21,3           4 3         
    5                  1 5         
    6,9                1 6         
    6,9                2 9         
    
      7 record(s) selected.
    2) Join with integer table(n).
    (You can generate integer table(n) by other ways,
    for example:using catalog table, recursive query)
    Code:
    WITH
     Test_Data(Col1) AS (
    VALUES
     '1,2,21,3'
    ,'5'
    ,'6,9'
    )
    /* End of Test_Data */
    ,Position_of_comma(col1, n, k) AS (
    SELECT col1
         , n
         , INTEGER( ROWNUMBER()
                    OVER(PARTITION BY col1 ORDER BY n) )
      FROM Test_Data
     INNER JOIN
           LATERAL
           (SELECT INTEGER( ROWNUMBER() OVER() )
              FROM (VALUES 0,1,2,3,4,5,6,7,8,9) N1(n1)
                 , (VALUES 0,1,2,3,4,5,6,7,8,9) N2(n2)
                 , (VALUES 0,1,2,3,4,5,6,7,8,9) N3(n3)
           /* Maximum length of col1 is assumed 998 */
            WHERE n1 + n2*10 + n3*100 <= LENGTH(col1) + 1
           ) N(n)
       ON  SUBSTR(','||col1||',', n, 1) = ','
    )
    SELECT p.col1
         , p.k
         , SUBSTR(p.col1, p.n, f.n-p.n-1) AS element
      FROM Position_of_comma p
         , LATERAL
           (SELECT n
              FROM Position_of_comma
             WHERE col1 = p.col1
               AND k    = p.k + 1
           ) f
    ;
    ------------------------------------------------------------------------------
    
    COL1     K           ELEMENT 
    -------- ----------- --------
    1,2,21,3           1 1       
    1,2,21,3           2 2       
    1,2,21,3           3 21      
    1,2,21,3           4 3       
    5                  1 5       
    6,9                1 6       
    6,9                2 9       
    
      7 record(s) selected.
    Last edited by tonkuma; 06-12-08 at 07:50.

  4. #4
    Join Date
    Jun 2008
    Posts
    10
    Thank you.

    ###############
    Regards
    Baseet Ahmed
    ###############

Posting Permissions

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