return all the occurrences of a substring
658834Sep 9 2008 — edited Sep 9 2008hi,
I have a problem with returning data from a list.
i can contain on the field values like (going after MTC):
* MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14
* MTCIN13;MTCIN14;100IN14;111IN14;123IN14
* MOCS13;MTCIN13;MTCIN14
* MOCS13;M100IN14;111IN14;123IN14
* ...
* 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?
thank you,
Ricardo Tomás