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!

Extract substring from a string in oracle

Discussion in 'SQL PL/SQL' started by laxman, Jul 23, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    I have one requirement related to extracting string from a paramater.

    suppose the string may like this in various format

    string:= 'This my string <Rid//problem/123456>'

    or
    string:= '<Rid//problem/123456> This my string'

    or

    string:= ' This is <Rid//problem/123456> my string'

    Now my requirement is i need to extract 123456 using pl/sql block.

    is there any way in oracle to get this thing done.

    Thanks n regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> DECLARE
      2  string     varchar2(100):= 'This my string <Rid//problem/123456>';
      3
      4  string2 varchar2(100):= '<Rid//problem/123456> This my string';
      5
      6  string3 varchar2(100):= ' This is <Rid//problem/123456> my string';
      7
      8  outstr varchar2(10);
      9
     10  BEGIN
     11          SELECT substr(string, instr(string, '>') -6, 6) INTO outstr FROM dual;
     12
     13          dbms_output.put_line(outstr);
     14
     15          SELECT substr(string2, instr(string2, '>') -6, 6) INTO outstr FROM dual;
     16
     17          dbms_output.put_line(outstr);
     18
     19          SELECT substr(string3, instr(string3, '>') -6, 6) INTO outstr FROM dual;
     20
     21          dbms_output.put_line(outstr);
     22
     23  END;
     24  /
    123456
    123456
    123456
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    As for your string we can write as below also using regular expressions.

    Code (Text):


    SQL>DECLARE
    2    string varchar2(100):= 'This my string <Rid//problem/123456>';
    3
    4    string2 varchar2(100):= '<Rid//problem/123456> This my string';
    5
    6    string3 varchar2(100):= ' This is <Rid//problem/123456> my string';
    7
    8    outstr varchar2(10);
    9
    10 BEGIN
    11
    12 select regexp_substr('This my string <Rid//problem/123456>','[[:digit:]]+')into outstr from dual;
    13 dbms_output.put_line(outstr);
    14
    15 select regexp_substr('<Rid//problem/123456> This my string','[[:digit:]]+') into outstr from dual;
    16 dbms_output.put_line(outstr);
    17
    18 select regexp_substr(' This is <Rid//problem/123456> my string','[[:digit:]]+') into outstr from dual;
    19 dbms_output.put_line(outstr);
    20
    21 END;
    22/
    123456
    123456
    123456

     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

     
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The general solution for this question will be.

    Code (SQL):
    SQL> WITH rec AS (
      2              SELECT 'This my string <Rid//problem/123456>' str FROM dual
      3              UNION
      4              SELECT '<Rid//problem/123456> This my string' FROM dual
      5              UNION
      6              SELECT 'This is <Rid//problem/123456> my string'FROM dual
      7              UNION
      8              SELECT 'This is <Rid//1237/1256> my string'FROM dual
      9              UNION
     10              SELECT 'This is <75678//1237/1256> my string'FROM dual
                )
     11   12  SELECT str ,  substr (str , instr (str,'/',-1)+1 ,
     13                      instr (str,'>',-1) - instr (str,'/',-1)-1) xstr
     14  FROM rec
     15  ;

    STR                                     XSTR
    --------------------------------------- ---------------------------------------
    <Rid//problem/123456> This my string    123456
    This IS <75678//1237/1256> my string    1256
    This IS <Rid//1237/1256> my string      1256
    This IS <Rid//problem/123456> my string 123456
    This my string <Rid//problem/123456>    123456

    SQL>
    This will take the string between last '/' and '>'.