In the process of scripting a database migration, I was in need of something akin to the GNU basename utility that I know and love on Linux.
basename is most famous for taking a full file path string and stripping away the leading path component, returning just the name of the file. This can be emulated in PL/SQL with calls to SUBSTR and INSTR, like this:substr(dirname,instr(dirname,'/',-1)+1)
(Thanks to Ian Cary, who shared this logic on oracle-l)
As you can see, this simply finds the last occurence of
/, which is our directory separator on *nix and Solaris operating systems. On Windows, it would be \. It then returns a substring beginning one character after that last separator until the end of the string. Voila, a basic basename routine!Upon reading the basename man page again, I found that basename also takes an optional parameter, a suffix string. If this suffix string is provided, basename will also truncate that string from the end. For example:
$ basename /home/seiler/bookmarks.html
bookmarks.html
$ basename /home/seiler/bookmarks.html .html
bookmarks
I decided that this would be handy to have, and set out to create a compatible basename function in PL/SQL. Here is what I came up with:
CREATE OR REPLACE FUNCTION basename (v_full_path IN VARCHAR2,
v_suffix IN VARCHAR2 DEFAULT NULL,
v_separator IN CHAR DEFAULT '/')
RETURN VARCHAR2
IS
v_basename VARCHAR2(256);
BEGIN
v_basename := SUBSTR(v_full_path, INSTR(v_full_path,v_separator,-1)+1);
IF v_suffix IS NOT NULL THEN
v_basename := SUBSTR(v_basename, 1, INSTR(v_basename, v_suffix, -1)-1);
END IF;
RETURN v_basename;
END;
/
I’ve also added an optional third parameter to specify a directory separator other than the default. It would probably be rarely useful, but not hard to remove if you don’t like it. As you can see, I’ve used similar SUBSTR/INSTR logic to identify the suffix index and prune it out.
Here it is in action:
SQL> COLUMN file_name FORMAT a45;
SQL> COLUMN basename FORMAT a15;
SQL> COLUMN no_suffix FORMAT a12;
SQL> SELECT file_name
2 , basename(file_name) as basename
3 , basename(file_name, '.dbf') as no_suffix
4 FROM dba_data_files;
FILE_NAME BASENAME NO_SUFFIX
--------------------------------------------- --------------- ------------
/u01/app/oracle/oradata/orcl/users01.dbf users01.dbf users01
/u01/app/oracle/oradata/orcl/sysaux01.dbf sysaux01.dbf sysaux01
/u01/app/oracle/oradata/orcl/undotbs01.dbf undotbs01.dbf undotbs01
/u01/app/oracle/oradata/orcl/system01.dbf system01.dbf system01
/u01/app/oracle/oradata/orcl/example01.dbf example01.dbf example01
I hope this makes your work just a little bit easier, as it has mine.