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 null
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 fastest solution. Speed matters because this will end up being ran against a very large table. This is what I have so far...
/*
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
So is there a fast way to accomplish this inline or should I just write this in a PL/SQL function?
This is what I'm working with...
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
UPDATE 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.
UPDATE Thanks for the answers so far. I ended up making a PL/SQL function that does what I need...
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;
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?
select regexp_substr(filename, '\.[^\.]*$') ext from testTable;
select extrip(filename) ext from testTable;
UPDATE
I added a order by ext
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 NOT return the dot "." though.