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?
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;