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/

Barbaros Özhan · Accepted Answer · 2022-03-29 17:31:33Z

2

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

edited Mar 29, 2022 at 17:31

answered Mar 29, 2022 at 17:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract the strings using regexp_substr in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related