GNU basename in PL/SQL

Posted in: Technical Track

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:


(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 
$ basename /home/seiler/bookmarks.html .html

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:

                                        v_suffix IN VARCHAR2 DEFAULT NULL,
                                        v_separator IN CHAR DEFAULT '/')
                v_basename VARCHAR2(256);
                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;

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.

Interested in working with Don? Schedule a tech call.

About the Author

Oracle database administrator for The Pythian Group, headquartered in Ottawa, Ontario, Canada. I am located in Manitowoc, Wisconsin, USA.OCP 10gR2 DBA

5 Comments. Leave new

What should this do:

SELECT basename(‘/user/bin/blah.dbf’, ‘dbx’) FROM dual;


10g+ option

SQL> select  filename
  2     , regexp_substr(filename, '[^/]*$') "just_filename"
  3  from t1;

FILENAME                       just_filename
------------------------------ ---------------
/lvl1/lvl2/lvl3/filename.txt   filename.txt   
/lvl1/lvl2/filename2.txt       filename2.txt  
filename3.txt                  filename3.txt

That should return “blah.” (with the period at the end). It works for me:

SQL> select basename('/usr/bin/blah.dbf','dbf') from dual;


This is precisely how GNU basename works.


@ebrian, thanks for the tip!

GNU basename in PL/SQL « die Seilerwerks
October 1, 2008 11:20 pm

[…] leave a comment » Reposted from The Pythian Group blog. […]


Leave a Reply

Your email address will not be published. Required fields are marked *