Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    20

    Post Unanswered: Update column value with File Name from path

    Hi,

    I need a solution to update a field value with it's underlying value.

    My Data
    Code:
    id    col1                      col2
    --    -----------------------   ---------------------
    1     a/b/d/file-name.jpg       folder1/file-name.gif
    2     some-fold/fol2/abc.jpeg   fold2/fold3/xyz
    ...
    ...
    Expected Output
    Code:
    id    col1          col2
    --    ---------     ---------
    1     file-name     file-name
    2     abc           xyz
    ...
    ...
    Please note that extensions can be 3/4 chars in length and the extension, i.e. the period and the text after that is optional in nature (may or may not present as in row 2 and col 2). I'm using DB2 v8.2.

    Any help is appreciated.


    Thanks in advance.
    Last edited by Rohit Oberoi; 11-22-11 at 12:41.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use an INSTRB user-defined-function in Sample UDFs for Migration

    Here is an example of using the UDF.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     My_Data(id , col1 , col2) AS (
    VALUES
      ( 1 , 'a/b/d/file-name.jpg'     , 'folder1/file-name.gif' )
    , ( 2 , 'some-fold/fol2/abc.jpeg' , 'fold2/fold3/xyz'       )
    )
    SELECT id
         , SUBSTR( col1
                 , INSTRB(col1 , '/' , -1) + 1
                 , LOCATE('.' , col1 || '.') - INSTRB(col1 , '/' , -1) - 1
                 ) AS new_col1
         , SUBSTR( col2
                 , INSTRB(col2 , '/' , -1) + 1
                 , LOCATE('.' , col2 || '.') - INSTRB(col2 , '/' , -1) - 1
                 ) AS new_col2
     FROM  My_Data
    ;
    ------------------------------------------------------------------------------
    
    ID          NEW_COL1                NEW_COL2             
    ----------- ----------------------- ---------------------
              1 file-name               file-name            
              2 abc                     xyz                  
    
      2 record(s) selected.
    An example of update statement using the expressions.

    Example 2:
    (Not tested.)
    Code:
    UPDATE My_Data
    SET (col1 , col2)
      = (  SUBSTR( col1
                 , INSTRB(col1 , '/' , -1) + 1
                 , LOCATE('.' , col1 || '.') - INSTRB(col1 , '/' , -1) - 1
                 )
         , SUBSTR( col2
                 , INSTRB(col2 , '/' , -1) + 1
                 , LOCATE('.' , col2 || '.') - INSTRB(col2 , '/' , -1) - 1
                 )
        )
    ;
    Last edited by tonkuma; 11-24-11 at 19:29. Reason: Add titles for examples("Example 1:" and "Example 2:")

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This would show better performance than previous example.
    Because, the UDF was called once for each column in this example,
    while it was called twice for each column in previous example.

    Example 3:
    Code:
    UPDATE My_Data
    SET (col1 , col2)
      = (SELECT
                SUBSTR( col1 , begin_fn1 , LOCATE('.' , col1 || '.') - begin_fn1 )
              , SUBSTR( col2 , begin_fn2 , LOCATE('.' , col2 || '.') - begin_fn2 )
          FROM  LATERAL
                (VALUES
                    (  INSTRB(col1 , '/' , -1) + 1
                     , INSTRB(col2 , '/' , -1) + 1
                    )
                ) s(begin_fn1 , begin_fn2)
        )
    ;
    Last edited by tonkuma; 11-24-11 at 19:30. Reason: Add title("Example 3:") for a sample code.

  4. #4
    Join Date
    Nov 2011
    Posts
    20
    Thanks tonkuma!

    But INSTRB() is not supported in my DB2 version (using DB2 v8.2). Will there be any other way to achive this?

    FYI, we've very restricted access, so UDF would be an issue also.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 4: extract file names without using UDF from columns which length are less than 1000.
    (Not tested on DB2 8.2)
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     my_data(id , col1 , col2) AS (
    VALUES
      ( 1 , 'a/b/d/file-name.jpg'     , 'folder1/file-name.gif' )
    , ( 2 , 'some-fold/fol2/abc.jpeg' , 'fold2/fold3/xyz'       )
    )
    SELECT id
         , SUBSTR( col1 , begin_fn1 , LOCATE('.' , col1 || '.') - begin_fn1 ) AS new_col1
         , SUBSTR( col2 , begin_fn2 , LOCATE('.' , col2 || '.') - begin_fn2 ) AS new_col2
     FROM  my_data
         , LATERAL
           (SELECT MAX( CASE col_id WHEN 1 THEN pos END ) + 1 AS begin_fn1
                 , MAX( CASE col_id WHEN 2 THEN pos END ) + 1 AS begin_fn2
             FROM  LATERAL
                   (VALUES (1 , col1)
                         , (2 , col2)
                   ) q(col_id , col )
             INNER JOIN
                   LATERAL
                   (SELECT p1 + p2 + p3
                     FROM  (VALUES   1  ,2  ,3  ,4  ,5  ,6  ,7  ,8  ,9  ,10) p(p1)
                         , (VALUES 0,10 ,20 ,30 ,40 ,50 ,60 ,70 ,80 ,90    ) p(p2)
                         , (VALUES 0,100,200,300,400,500,600,700,800,900   ) p(p3)
                     WHERE p1 + p2 + p3 <= LENGTH(col)
                   ) p(pos)
               ON  SUBSTR(col , pos , 1) = '/'
           ) s
    ;
    ------------------------------------------------------------------------------
    
    ID          NEW_COL1                NEW_COL2             
    ----------- ----------------------- ---------------------
              1 file-name               file-name            
              2 abc                     xyz                  
    
      2 record(s) selected.
    Example 5: update statement by using row-fullselect in Example 4.
    (Not tested on DB2 8.2)
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE my_data
    SET (col1 , col2) = (
    SELECT SUBSTR( col1 , begin_fn1 , LOCATE('.' , col1 || '.') - begin_fn1 ) AS new_col1
         , SUBSTR( col2 , begin_fn2 , LOCATE('.' , col2 || '.') - begin_fn2 ) AS new_col2
     FROM  LATERAL
           (SELECT MAX( CASE col_id WHEN 1 THEN pos END ) + 1 AS begin_fn1
                 , MAX( CASE col_id WHEN 2 THEN pos END ) + 1 AS begin_fn2
             FROM  LATERAL
                   (VALUES (1 , col1)
                         , (2 , col2)
                   ) q(col_id , col )
             INNER JOIN
                   LATERAL
                   (SELECT p1 + p2 + p3
                     FROM  (VALUES   1  ,2  ,3  ,4  ,5  ,6  ,7  ,8  ,9  ,10) p(p1)
                         , (VALUES 0,10 ,20 ,30 ,40 ,50 ,60 ,70 ,80 ,90    ) p(p2)
                         , (VALUES 0,100,200,300,400,500,600,700,800,900   ) p(p3)
                     WHERE p1 + p2 + p3 <= LENGTH(col)
                   ) p(pos)
               ON  SUBSTR(col , pos , 1) = '/'
           ) s
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM my_data;
    ------------------------------------------------------------------------------
    
    ID          COL1                           COL2                          
    ----------- ------------------------------ ------------------------------
              1 file-name                      file-name                     
              2 abc                            xyz                           
    
      2 record(s) selected.
    Last edited by tonkuma; 11-24-11 at 19:50. Reason: Adjust blanks in sample codes

  6. #6
    Join Date
    Nov 2011
    Posts
    20

    Thumbs up

    Incredible answer. Thank you very much tonkuma!

Tags for this Thread

Posting Permissions

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