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!

Question on regexp_substr

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Oct 12, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi,
    I have data in a column
    #abcd#efghi#jlmn#opqrs

    I need to retrieve values between '#'. I need to get output as below

    var1 = abcd
    var2 = efghi
    var3 = jlmn
    var4 = opqrs

    Similary if column value is

    #abcd#efghi#jlmnopqrs

    var1 = abcd
    var2 = efghi
    var3 = jlmnopqrs
    var4 = null

    how to achieve this? kindly help
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Code (SQL):
    SELECT regexp_substr('#abcd#efghi#jlmnopqrs','[^#]+',1,level) FROM dual
    CONNECT BY  regexp_substr('#abcd#efghi#jlmnopqrs','[^#]+',1,level) IS NOT NULL;
     
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thank you very much :)