Update column value with File Name from path

Rohit Oberoi · #1 (**permalink**) 11-22-11, 11:38

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.

tonkuma · #2 (**permalink**) 11-22-11, 15:59

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
             )
    )
;

tonkuma · #3 (**permalink**) 11-22-11, 20:51

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)
    )
;

Rohit Oberoi · #4 (**permalink**) 11-24-11, 11:07

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.

tonkuma · #5 (**permalink**) 11-24-11, 18:39

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.

Rohit Oberoi · #6 (**permalink**) 11-24-11, 22:42

Incredible answer. Thank you very much tonkuma!

Internet Services

Web Development

Digital Marketing

Consumer Tech