title: Oracle: Fastest Way to Extract Filename Extension Using SQL or PL/SQL
body: <p>I need to get the extensions of filenames.  Extensions could be any length (not just 3) and they could also be non-existent, in which case I need <code>null</code> returned.  I know I could easily write a PL/SQL function that does this then just call that function in the query but I was hoping that I could somehow do it all inline.  And I don't really care how long the solution is, what I need is the <strong>fastest</strong> solution.  Speed matters because this will end up being ran against a very large table.  This is what I have so far...</p>
  
  <pre><code>/*
  The same method is being used in all 5 examples.
  It works for all of them except the first one.
  The first one I need to return null
  */
  
  SELECT substr(filename,instr(filename,'.',-1)+1,length(filename)-instr(filename,'.',-1))
    FROM (select 'no_extension_should_return_null' filename from dual);
  --returns: no_extension_should_return_null
  
  SELECT substr(filename,instr(filename,'.',-1)+1,length(filename)-instr(filename,'.',-1))
    FROM (select 'another.test.1' filename from dual);
  --returns: 1
  
  SELECT substr(filename,instr(filename,'.',-1)+1,length(filename)-instr(filename,'.',-1))
    FROM (select 'another.test.doc' filename from dual);
  --returns: doc
  
  SELECT substr(filename,instr(filename,'.',-1)+1,length(filename)-instr(filename,'.',-1))
    FROM (select 'another.test.docx' filename from dual);
  --returns: docx
  
  SELECT substr(filename,instr(filename,'.',-1)+1,length(filename)-instr(filename,'.',-1))
    FROM (select 'another.test.stupidlong' filename from dual);
  --returns: stupidlong
  </code></pre>
  
  <p>So is there a fast way to accomplish this inline or should I just write this in a PL/SQL function?</p>
  
  <p>This is what I'm working with...</p>
  
  <pre><code>select * from v$version;
  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  PL/SQL Release 11.2.0.2.0 - Production
  CORE    11.2.0.2.0  Production
  TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
  NLSRTL Version 11.2.0.2.0 - Production
  </code></pre>
  
  <p><strong>UPDATE</strong>
  I'm moving this code into a function and will setup a test to call it a million times to see if the function slows it down, I'm thinking it won't make an impact since it's just string manipulation.</p>
  
  <p><strong>UPDATE</strong>
  Thanks for the answers so far.  I ended up making a PL/SQL function that does what I need...</p>
  
  <pre><code>create or replace function extrip(filename varchar2) return varchar2 as
  begin
      if ( instr(filename,'.',-1) = 0 ) then
          return null;
      end if;
  
      return substr(filename,instr(filename,'.',-1)+1,length(filename)-instr(filename,'.',-1));
  end;
  </code></pre>
  
  <p>I then ran two tests against a table with 2 million rows.  When I viewed the explain plan for both they were 100% IDENTICAL.  How could that be?</p>
  
  <pre><code>select regexp_substr(filename, '\.[^\.]*$') ext from testTable;
  
  select extrip(filename) ext from testTable;
  </code></pre>
  
  <p><strong>UPDATE</strong>
  I added a <code>order by ext</code> to both of those then reran the tests and there was a difference.  The regexp took 9sec and the function took 17sec.  I guess without the order by TOAD was just retrning the first X number of recs.  So @Brian McGinity was right.  I still need the regexp method to <strong>NOT</strong> return the dot "." though.</p>