Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
    
Works Perfectly. Thank you! –  Glen Robson Oct 16 '12 at 8:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.