Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Gianni Ceresa

What OS are you using on your laptop?

Gianni Ceresa

And, suspecting what reply will come, is your OS certified?
Administering Oracle Analytics Server (0 Bytes)If your laptop is using Windows 10 or Windows 11, the tool isn't supposed to work there, and nobody will be able to do much for you.

BrijeshPatel

Thanks for swift reply Gianni
I have windows 2012 R2 Server on Windows server and Windows 10 Pro on (Laptop). Both have the same issue.
Installation no issue at all, till last completion step.
Thank you

Gianni Ceresa

Do not install on Windows 10, it isn't built to work there. Being able to install doesn't mean you can use the product (just like going on an off-road safari with a F1 car: you will be able to start the engine, but will not move much).
On a Windows Server is it supposed to work, but this thread is about your laptop with Windows 10, so it's another problem.

BrijeshPatel

Thank you for suggestion . I will not go forward with windows 10 env.
Let me check with Windows 2012 and create new thread to find out the issue.

Great help.
Thank you

1 - 5
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
27,537 views