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 amanda121287, Apr 13, 2018.

  1. amanda121287

    amanda121287 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    3594 Tator Patch Road Westchester, IL 60154
    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:
    765
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    for example: REGEXP_SUBSTR
    Code (SQL):

    SELECT
    'var'||level||' = '||regexp_substr('#abcd#efghi#jlmn#opqrs','[^#]+',1,level)
    FROM dual
    CONNECT BY regexp_substr('#abcd#efghi#jlmn#opqrs','[^#]+',1,level) IS  NOT NULL;