Getting the page name from a web URL in Oracle

Question

I have a list of page URL's in my database table under the column heading URL. There are many different prefixes on each page name for example:

Test1/Admin/Page7.asp
Test1/One/Page3.asp
Test1/Another/Page2.asp
Testing1/Admin2/Page12.asp

Currently I have created some SQL to get a distinct list of the above URL's. However what i need is just the page names. I need to get rid of any text before the last "/". How would I be able to do this within Oracle?

My SQL currently looks something like this:

SELECT DISTINCT URL FROM TABLE1 WHERE URL IS NOT NULL ORDER BY TABLE1.URL

Answer 1

For something more complex you might need a regular expression, but in this case, the following should suffice.

SELECT DISTINCT SUBSTR(URL,INSTR(URL,'/',-1)+1) 
FROM TABLE1 
WHERE URL IS NOT NULL 
ORDER BY 1;

Sample data.

create table table1 (URL Varchar2(1000));
insert into table1 values ('Test1/Admin/Page7.asp');
insert into table1 values ('Test1/One/Page3.asp');
insert into table1 values ('Test1/Another/Page2.asp');
insert into table1 values ('Testing1/Admin2/Page12.asp');
insert into table1 values ('Page7.asp');
commit;