1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to split the values by 2 using substring?

Discussion in 'SQL PL/SQL' started by Vicky, Jul 26, 2016.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    I've tried to split the values by 2 using substring, but got ended in something like this..

    Code (SQL):
    SELECT SUBSTR('123456',level,level*2) FROM dual CONNECT BY level<=LENGTH('123456')/2;
    Actual Output:
    ------------------
    SUBSTR
    ------
    12
    2345
    3456


    Expected Output:
    ----------------------
    SUBSTR
    ------
    12
    34
    56
     
  2. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    SELECT SUBSTR ( '123456' , (2 * LEVEL) -1, 2) FROM dual CONNECT BY LEVEL <= LENGTH ('123456') / 2;
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Superb Sid! Many thanks!!:)
     
    Narendra B likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yet when there are more rows than just 1, or the length of the column isn't an even number it fails, either by going into a long LEVEL loop returning useless text or it fails to return the entire string in consecutive groups of two letters. It's an interesting approach but it isn't an actual solution since it doesn't generalize and process a table full of data correctly.