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
INSTR, like this:
(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.