Need SQL statement

ddas · #1 (**permalink**) 05-17-11, 04:43

Hi Experts,

I need a help on DB2 SQL statement, my data is mentioned below with '/' delimiter in a single column and the output I want it to put in different columns as shown below.

Data
Col A
/apple
/apple/banana
/orange/apple/banana
/mango/apple/orange/fig

Output
Col A Col B Col C Col D
apple null null null
apple banana null null
orange apple banana null
mango apple orange fig

tonkuma · #2 (**permalink**) 05-17-11, 08:50

Perhaps, we can argue over table/column design issue.

Apart from the design issue, my first idea was using recursive query.

It may be easy, if you are used to recursive query.
So, I'll give other examples.

Note: I assumed that '/' in first character was guaranteed.

Example 1:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 Data(Col_A) AS (
VALUES
  '/apple'
, '/apple/banana'
, '/orange/apple/banana'
, '/mango/apple/orange/fig'
)
SELECT d.col_a
     , MAX( CASE rn
            WHEN 1 THEN
                 SUBSTR(col_a , 2 , lead_k - 2)
            END
          ) AS new_a
     , MAX( CASE rn
            WHEN 2 THEN
                 SUBSTR(col_a , k + 1 , lead_k - k - 1)
            END
          ) AS new_b
     , MAX( CASE rn
            WHEN 3 THEN
                 SUBSTR(col_a , k + 1 , lead_k - k - 1)
            END
          ) AS new_c
     , MAX( CASE rn
            WHEN 4 THEN
                 SUBSTR(col_a , k + 1 , lead_k - k - 1)
            END
          ) AS new_d
 FROM  (SELECT col_a
             , k
             , ROW_NUMBER()
                 OVER(PARTITION BY col_a
                          ORDER BY k    ) rn
             , LEAD(k)
                 OVER(PARTITION BY col_a
                          ORDER BY k    ) lead_k
         FROM  data
         INNER JOIN
               (VALUES     1, 2, 3, 4, 5, 6, 7, 8, 9
                      ,10,11,12,13,14,15,16,17,18,19
                      ,20,21,22,23,24,25,26,27,28,29
                      ,30,31,32,33,34,45,46,47,38,39
                      ,40,41,42,43,44,45,46,47,48,49) p(k)
          ON  k = 1
          OR  K BETWEEN 2 AND LENGTH(col_a) + 1
          AND SUBSTR(col_a || '/' , MIN( k , LENGTH(col_a) + 1 ) , 1) = '/'
      ) d
 GROUP BY
       col_a
;
------------------------------------------------------------------------------

COL_A                   NEW_A                   NEW_B                   NEW_C                   NEW_D                  
----------------------- ----------------------- ----------------------- ----------------------- -----------------------
/apple                  apple                   -                       -                       -                      
/apple/banana           apple                   banana                  -                       -                      
/mango/apple/orange/fig mango                   apple                   orange                  fig                    
/orange/apple/banana    orange                  apple                   banana                  -                      

  4 record(s) selected.

Example 2:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 Data(Col_A) AS (
VALUES
  '/apple'
, '/apple/banana'
, '/orange/apple/banana'
, '/mango/apple/orange/fig'
)
SELECT d.col_a
     , SUBSTR(  col_a
              , 2
              , INSTR(col_a || '/' , '/' , 1 , 2) - 2
             ) AS new_a
     , SUBSTR(  col_a
              , INSTR(col_a , '/' , 1 , 2) + 1
              , NULLIF( INSTR(col_a || '/' , '/' , 1 , 3) , 0 )
                -
                INSTR(col_a , '/' , 1 , 2) - 1
             ) AS new_b
     , SUBSTR(  col_a
              , INSTR(col_a , '/' , 1 , 3) + 1
              , NULLIF( INSTR(col_a || '/' , '/' , 1 , 4) , 0 )
                -
                INSTR(col_a , '/' , 1 , 3) - 1
             ) AS new_c
     , SUBSTR(  col_a
              , INSTR(col_a , '/' , 1 , 4) + 1
              , NULLIF( INSTR(col_a || '/' , '/' , 1 , 5) , 0 )
                -
                INSTR(col_a , '/' , 1 , 4) - 1
             ) AS new_d
 FROM  data d
;
------------------------------------------------------------------------------

COL_A                   NEW_A                   NEW_B                   NEW_C                   NEW_D                  
----------------------- ----------------------- ----------------------- ----------------------- -----------------------
/apple                  apple                   -                       -                       -                      
/apple/banana           apple                   banana                  -                       -                      
/orange/apple/banana    orange                  apple                   banana                  -                      
/mango/apple/orange/fig mango                   apple                   orange                  fig                    

  4 record(s) selected.

ddas · #3 (**permalink**) 05-17-11, 09:44

Thanks a lot

tonkuma · #4 (**permalink**) 05-17-11, 16:21

Example 2-1:

Make Example 2 shorter by using sub-query.

Code:

SELECT col_a
     , SUBSTR(col_a , 2         ,        sep_2      - 2        ) AS new_a
     , SUBSTR(col_a , sep_2 + 1 , NULLIF(sep_3 , 0) - sep_2 - 1) AS new_b
     , SUBSTR(col_a , sep_3 + 1 , NULLIF(sep_4 , 0) - sep_3 - 1) AS new_c
     , SUBSTR(col_a , sep_4 + 1 , NULLIF(sep_5 , 0) - sep_4 - 1) AS new_d
 FROM  (SELECT col_a
             , INSTR(col_a || '/' , '/' , 1 , 2) AS sep_2
             , INSTR(col_a || '/' , '/' , 1 , 3) AS sep_3
             , INSTR(col_a || '/' , '/' , 1 , 4) AS sep_4
             , INSTR(col_a || '/' , '/' , 1 , 5) AS sep_5
         FROM  data
       ) d
;

stolze · #5 (**permalink**) 05-19-11, 00:57

I wrote an article on parsing strings in SQL a few years back: Parsing Strings in SQL You can adapt this to put the parsed entries into separate columns instead of rows.

Internet Services

Web Development

Digital Marketing

Consumer Tech