return all the occurrences of a substring
658834Sep 9 2008 — edited Sep 9 2008
I have a problem with returning data from a list.
i can contain on the field values like (going after MTC):
* MTCXXX can have different size and can be anywhere in the field.
* The information cames from a list, so it is ordered, if there is more then one MTC it will came in the front.
* "MTC" exists always;
first i only need to check if the information existed, so i made this:
DECODE(REGEXP_SUBSTR(UPPER(CONCAT(';',List)),UPPER(';mtc^;+')),null,'FALSE','TRUE') AS MTC
and it was OK
then it was asked to me to return the complete value and i made this:
NVL(SUBSTR(REGEXP_SUBSTR(UPPER(CONCAT(';',List)),UPPER(';mtc;+')),LENGTH(';mtc')-2,LENGTH(REGEXP_SUBSTR(CONCAT(';',List),';mtc;+'))),' ') AS MTC
now that i made this i was told that there can be more than one occurrence and that i must return all the values!
i ordered the list to make this easy but now I'm not being able to get the right expression to get the complete set of values!
can someone help me?