Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: DB2 split alphanumeric values

    I have the values in a column as:
    'asdf1230'
    ,'11134asd'
    ,ssd112wwe'

    I want to split the string as the prefix of characters and suffix of characters and insert into another table using DB2 queries(see below):

    result should be:

    asdf 1230 <blank>
    <blank> 11134 asd
    ssd 112 wwe

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(in_string) AS (
    VALUES
      'asdf1230'
    , '11134asd'
    , 'ssd112wwe'
    )
    SELECT in_string
         , LEFT(in_string , pos_num - 1)         AS first_col
         , SUBSTR(in_string , pos_num , len_num) AS second_col
         , SUBSTR(in_string , pos_num + len_num) AS third_col
     FROM  (SELECT in_string
                 , LOCATE( ' ' , TRANSLATE(in_string , ' ' , '0123456789') )
                 , LENGTH(in_string)
                   - LENGTH( TRANSLATE(in_string , '' , '0123456789' , '') )
             FROM  test_data
           ) q(in_string , pos_num , len_num)
    ;
    ------------------------------------------------------------------------------
    
    IN_STRING FIRST_COL SECOND_COL THIRD_COL
    --------- --------- ---------- ---------
    asdf1230  asdf      1230                
    11134asd            11134      asd      
    ssd112wwe ssd       112        wwe      
    
      3 record(s) selected.
    Last edited by tonkuma; 10-05-11 at 10:24.

  3. #3
    Join Date
    Oct 2011
    Posts
    2

    DB2 split alphanumeric values

    Thank you for your guidence:

    Actually my request is like:
    I have three columns like
    firstcol
    secondcol
    thirdcol

    The values are in the secondcol
    'asdf1230'
    , '11134asd'
    , 'ssd112wwe'


    I want to update the firstcol and thirddcol based on the request(previously) on fly and need to leave only numeric in the secondcol.
    firstcol secondcol thirdcol
    asdf 1230 <blank>
    <blank> 11134 asd
    ssd 112 wwe


    I forgot to add the occurence of the another data:
    'awd023s-sd234'

    this case should be:
    firstcol secondcol thirdcol
    awd 023 s-sd234

    Regards
    Muthu

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please write your complete requirement at first.

    This is not a practice nor homework, I thought.
    So, you should NOT show the requirements from simple one first, then add additional requirements step by step.


    Anyhow, here is a revised example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(secondcol) AS (
    VALUES
      'asdf1230'
    , '11134asd'
    , 'ssd112wwe'
    , 'awd023s-sd234'
    )
    SELECT secondcol
         , LEFT  (secondcol , pos_num - 1)       AS new_firstcol
         , SUBSTR(secondcol , pos_num , len_num) AS new_secondcol
         , SUBSTR(secondcol , pos_num + len_num) AS new_thirdcol
     FROM  (SELECT secondcol
                 , pos_num
                 , LENGTH(secondcol)
                   - pos_num + 1
                   - LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) ) AS len_num
             FROM  (SELECT secondcol
                         , blank_num
                         , LOCATE( ' ' , blank_num ) AS pos_num
                     FROM  (SELECT secondcol
                                 , TRANSLATE(secondcol , ' ' , '0123456789') AS blank_num
                             FROM  test_data
                           ) p
                   ) q
           ) r
    ;
    ------------------------------------------------------------------------------
    
    SECONDCOL     NEW_FIRSTCOL  NEW_SECONDCOL NEW_THIRDCOL 
    ------------- ------------- ------------- -------------
    asdf1230      asdf          1230                       
    11134asd                    11134         asd          
    ssd112wwe     ssd           112           wwe          
    awd023s-sd234 awd           023           s-sd234      
    
      4 record(s) selected.
    The example of update statement might be:
    Code:
    /* Not tested. */
    UPDATE <your table>
       SET (firstcol , secondcol , thirdcol)
           = (/* Put the query example with removing secondcol. */
              SELECT LEFT  (secondcol , pos_num - 1)
                   , SUBSTR(secondcol , pos_num , len_num)
                   , SUBSTR(secondcol , pos_num + len_num)
               FROM  LATERAL
                     (SELECT pos_num
                           , LENGTH(secondcol)
                             - pos_num + 1
                             - LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) ) AS len_num
                       FROM  LATERAL
                             (SELECT blank_num
                                   , LOCATE( ' ' , blank_num ) AS pos_num
                               FROM  LATERAL
                                     (VALUES TRANSLATE(secondcol , ' ' , '0123456789')
                                     ) p(blank_num)
                             ) q
                     ) r
             )
    ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This example may be easier to read by removing nesting of subqueries(nested-table-expressions).
    Code:
    UPDATE <your table>
       SET (firstcol , secondcol , thirdcol)
         = (/* Put the query example. */
            SELECT LEFT  (secondcol , pos_num - 1)
                 , SUBSTR(secondcol , pos_num , len_num)
                 , SUBSTR(secondcol , pos_num + len_num)
             FROM  LATERAL
                   (VALUES TRANSLATE(secondcol , ' ' , '0123456789')
                   ) p(blank_num)
                 , LATERAL
                   (VALUES LOCATE( ' ' , blank_num )
                   ) q(pos_num)
                 , LATERAL
                   (VALUES LENGTH(secondcol)
                           - pos_num + 1
                           - LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) )
                   ) r(len_num)
           )
    ;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a test result of the query(row-fullselect) inside the last update example.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(secondcol) AS (
    VALUES
      'asdf1230'
    , '11134asd'
    , 'ssd112wwe'
    , 'awd023s-sd234'
    )
    SELECT *
     FROM  (SELECT secondcol
                 , LEFT  (secondcol , pos_num - 1)
                 , SUBSTR(secondcol , pos_num , len_num)
                 , SUBSTR(secondcol , pos_num + len_num)
             FROM  test_data
                 , LATERAL
                   (VALUES TRANSLATE(secondcol , ' ' , '0123456789')
                   ) p(blank_num)
                 , LATERAL
                   (VALUES LOCATE( ' ' , blank_num )
                   ) q(pos_num)
                 , LATERAL
                   (VALUES LENGTH(secondcol)
                           - pos_num + 1
                           - LENGTH( LTRIM( SUBSTR(blank_num , pos_num) ) )
                   ) r(len_num)
           )
    ;
    ------------------------------------------------------------------------------
    
    SECONDCOL     2             3             4            
    ------------- ------------- ------------- -------------
    asdf1230      asdf          1230                       
    11134asd                    11134         asd          
    ssd112wwe     ssd           112           wwe          
    awd023s-sd234 awd           023           s-sd234      
    
      4 record(s) selected.

Posting Permissions

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