oracle-databaseextractsubst

oracle 8 extract strings from string


I have

"This is <>sample<> text. Need to extract <>smth1<> and <>smth2<> and many others." in a column of oracle database. I need to get:

sample smth1 smth2

Any help?


Solution

  • try to create this function:

    create or replace
    Function GetSurroundedText (origStr varchar2, openingStr varchar2, closingStr varchar2, outputSep varchar2)
    Return Varchar2
    Is
       continue boolean := true;
       l_string Varchar2(2000) := origStr;
       startPos PLS_Integer;
       endPos PLS_Integer;
       openingStrPos PLS_Integer;
       res Varchar2(2000) := '';
       sep Varchar2(100) := outputSep;
    Begin
    While true
    Loop
       openingStrPos :=Instr(l_string, openingStr);
       If openingStrPos > 0 Then
          startPos := openingStrPos + Length(openingStr);
          l_String := Substr(l_string, startPos);
       else
          exit;
       end if;
       endPos := Instr(l_string, closingStr);
       if endPos > 0 Then
          if res = '' Then
             sep := '';
          else
             sep := outputSep;
          end If;
          res := res || sep || Substr(l_string, 1, endpos-1);
          l_String := Substr(l_string, endPos + Length(closingStr));
       else
          exit;
       end if;
    End Loop;
    return res;
    End;
    

    And, in your case, use it like this:

    select GetSurroundedText(mycolumn, '<>', '<>', ' ') from mytable;