I am trying to find a way in oracle SQL to take all the text after the 1st "/" FROM THE RIGHT to the 4th "/". I need to do this in one SQL statement..
table name: access_log
col name: download
value: Download file:/webdocs/data/groupXXX/case/03_28_54_9_0000011856.pdf
I am trying to end up with
case
then a count of how many matches? can this be done?
select regexp_replace('Download file:/webdocs/data/groupXXX/case/03_28_54_9_0000011856.pdf',
'.*/([^/]+)/[^/]*$', '\1') from dual;
A Simple SUBSTR approach works from Oracle 10g+
select TRIM(BOTH '/' FROM
substr(
download,
INSTR(download,'/',1,4),
INSTR(download,'/',1,5)-INSTR(download,'/',1,4)
)
)
FROM ACCESS_LOG;
And count could be,
SELECT
COUNT(
CASE WHEN TRIM(BOTH '/' FROM
substr(
download,
INSTR(download,'/',1,4),
INSTR(download,'/',1,5)-INSTR(download,'/',1,4)
)
) = 'case'
THEN
1
END
) COUNT_MATCHED
FROM
ACCESS_LOG;
