Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    62

    Question Unanswered: How to create multi rows from a single row

    i have 1 row with 1 field with length 400 characters. How do i split this value to 4 rows of 100 chars each row.

    Example
    1,"Help me to solve"

    will be split to
    1,"Help "
    2,"me "
    3,"to "
    4,"solve "

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Code:
    SQL> var x varchar2(400)
    SQL> exec :x := 'Help me to solve';
    
    PL/SQL procedure successfully completed.
    
    SQL> with data
      2    as (
      3  select ' ' || :x || ' ' x
      4    from dual
      5       )
      6  select level, substr( x,
      7                        instr( x, ' ', 1, level ) + 1,
      8                        instr( x, ' ', 1, level + 1 ) - instr( x, ' ', 1, level ) - 1 ) x
      9    from data
     10  connect by level <= length( trim( x ) ) - length( replace( trim( x ), ' ', '' ) ) + 1
     11  /
    
         LEVEL X
    ---------- --------------------------------------------------
             1 Help
             2 me
             3 to
             4 solve
    
    SQL>
    That goes with the example of data you posted, however, it doesn't exactly answer your question (indeed, your question and data example doesn't correspond each other). I believe, for the 100 char split, you need something like this
    Code:
    SQL> with data
      2    as (
      3  select lpad( 'x', 100, 'x' ) ||
      4         lpad( 1, 100, 1 ) ||
      5         lpad( 2, 100, 2 ) ||
      6         lpad( 3, 100, 3 ) ||
      7         lpad( 4, 100, 4 ) ||
      8         lpad( 'x', 100, 'x' ) x
      9    from dual
     10       )
     11  select level, substr( x,
     12                        ( level * 100 ) + 1,
     13                        ( ( level + 1 ) * 100 ) - ( ( level * 100 ) ) - 1 ) x
     14    from data
     15  connect by level <= trunc( length( x ) / 100 ) - 2
     16  /
    
         LEVEL X
    ---------- --------------------------------------------------------------------------------------------------------------
             1 111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
             2 222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
             3 333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
             4 444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444
    
    SQL>

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    another variation

    var x varchar2(400)
    exec :x := 'Help me to solve';


    INSERT INTO NEW_TABLE
    with data
    as (
    select ' ' || :x || ' ' x
    from dual
    )
    select level, substr( x,
    instr( x, ' ', 1, level ) + 1,
    instr( x, ' ', 1, level + 1 ) - instr( x, ' ', 1, level ) - 1 ) x
    from data
    connect by level <= length( trim( x ) ) - length( replace( trim( x ), ' ', '' ) ) + 1
    /

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    However, if he really means what he said. Then do the following

    alter mytable add (col2 varchar2(100),col3 varchar2(100),col4 varchar2(100));

    update mytable
    set col2 = substr(col1,101,100),
    col3 = substr(col1,201,100),
    col4 = substr(col1,301),
    col1 = substr(col1,1,100);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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