Skip to Main Content

SQL & PL/SQL

return all the occurrences of a substring

658834Sep 9 2008 — edited Sep 9 2008
hi,
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
This post has been answered by BluShadow on Sep 9 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 7 2008
Added on Sep 9 2008
17 comments
26,184 views