Extract the strings using regexp_substr in oracle

Question

I use regexp_substr function to extract strings in oracle database. I need to remove only the last string column and print the remaining output.

Thanks in advance

Original datafile location

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/apps01/oradata/orcl/datafile/o1_mf_system_k45ylzb0_.dbf
/apps01/oradata/orcl/datafile/o1_mf_sysaux_k45yn2fs_.dbf
/apps01/oradata/orcl/datafile/o1_mf_undotbs1_k45ynvjz_.dbf
/apps01/oradata/orcl/datafile/o1_mf_users_k2pmzsw2_.dbf
/apps01/oradata/orcl/datafile/o1_mf_users_k45ynwlp_.dbf

I need to remove last string column with *.dbf similar to awk in linux

SQL:

SQL> select regexp_substr(name, '/[A-Z].*$') from v$datafile;

REGEXP_SUBSTR(NAME,'/[A-Z].*$')
--------------------------------------------------------------------------------
/orcl/datafile/o1_mf_system_k45ylzb0_.dbf
/orcl/datafile/o1_mf_sysaux_k45yn2fs_.dbf
/orcl/datafile/o1_mf_undotbs1_k45ynvjz_.dbf
/orcl/datafile/o1_mf_users_k2pmzsw2_.dbf
/orcl/datafile/o1_mf_users_k45ynwlp_.dbf

Expected Output:

NAME
--------------------------------------------------------------------------------
/apps01/oradata/orcl/datafile/
/apps01/oradata/orcl/datafile/
/apps01/oradata/orcl/datafile/
/apps01/oradata/orcl/datafile/
/apps01/oradata/orcl/datafile/

Answer 1

You can use

• combination of SUBSTR() and INSTR() functions by searching for / character reversely through use of -1 as the last parameter for INSTR()

or

• REGEXP_REPLACE() with [^/]+$ pattern where

  + matches one or more occurrences,

  $ matches the end of a string

as the second method

such as

SELECT SUBSTR(name,1,INSTR(name,'/',-1)) AS method1,
       REGEXP_REPLACE(name,'[^/]+$') AS method2 
  FROM v$datafile

Demo