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!

sql query help - extract all the occurrences of url links from string

ragu_sAug 9 2018 — edited Aug 9 2018

Hello Folks,

I need some help in extracting all the url links from the below string.

... https://mycomp.jiveon.com/groups/ser-se-ppo271/pages/home Jive page. On 31 Jan ’11, test user ... https://mycomp.jiveon.com/groups/ser-se-ppo7001/pages/stee-com for membership) is a management ... (https://mycomp.jiveon.com/groups/ser-se-tyy001/pages/training). Implemented procedures and other ... https://mycomp.jiveon.com/docs/DOC-12345 Risk Assessment within Groups. ...

I tried the below query, but no success as I was able to extract only the first occurrence. Their can be n.. number of occurrence within the string, all i wanted is to extract all the jiveon url links from the string as a semi-colon separated fashion. Please help me out with this.

SELECT REGEXP_SUBSTR (

'... https://mycomp.jiveon.com/groups/ser-se-ppo271/pages/home Jive page. On 31 Jan ’11, test user ... https://mycomp.jiveon.com/groups/ser-se-ppo7001/pages/stee-com for membership) is a management ... (https://mycomp.jiveon.com/groups/ser-se-tyy001/pages/training). Implemented procedures and other ... https://mycomp.jiveon.com/docs/DOC-12345 Risk Assessment within Groups. ...',

'[^https://mycomp.jiveon.com/](\S*)')

url_link

FROM DUAL;

Output:

https://mycomp.jiveon.com/groups/ser-se-ppo271/pages/home

Expected Output:

https://mycomp.jiveon.com/groups/ser-se-ppo271/pages/home;https://mycomp.jiveon.com/groups/ser-se-ppo7001/pages/stee-com;https://mycomp.jiveon.com/groups/ser-se-tyy001/pages/training;https://mycomp.jiveon.com/docs/DOC-12345

Thanks in advance.

-RS