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?
-
2you're really using Oracle 8?tbone– tbone2013-02-14 13:02:58 +00:00Commented Feb 14, 2013 at 13:02
Add a comment
|
3 Answers
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;
Comments
In Oracle/PLSQL, the replace function replaces a sequence of characters in a string with another set of characters.
replace( string1, string_to_replace, [ replacement_string ])
so
replace( YourString, '<>', '');
Should do the trick.
If your case is more complex and you need a more elaborated solution you can check this function that allow you extract words between delimiters.
http://www.oradev.com/parse_string.jsp
Hope it helps.
Comments
Use replace function to replace the <> to ''.It will work
