sql - Oracle:使用SQL或PL/SQL提取文件扩展名的最快方法

Question

我需要获取文件名的扩展名。扩展名可以是任意长度(不只是3个)，也可以不存在，在这种情况下，我需要返回null。我知道我可以轻松编写一个执行此操作的PL/SQL函数，然后仅在查询中调用该函数，但我希望可以以某种方式内联完成所有操作。而且我不太在乎解决方案有多长时间，我需要的是最快的解决方案。速度很重要，因为最终会碰到很大的 table 。这是我到目前为止所拥有的...

/*
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

因此，有没有一种快速的方法可以完成此内联操作，还是应该只在PL/SQL函数中编写它？

这就是我正在使用的...

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

更新
我正在将此代码移入一个函数，并将设置一个测试以使其调用一百万次，以查看该函数是否降低了速度，我认为它不会产生影响，因为它只是字符串操作。

更新
到目前为止，感谢您的回答。我最终做了一个PL/SQL函数来满足我的需要...

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;

然后，我对具有200万行的表进行了两次测试。当我查看两者的解释计划时，它们都是100％相同的。怎么会这样

select regexp_substr(filename, '\.[^\.]*$') ext from testTable;

select extrip(filename) ext from testTable;

更新
我向这两个都添加了 order by ext，然后重新运行了测试，但有所不同。正则表达式花了9秒，函数花了17秒。我想如果没有TOAD的命令，则只能恢复记录的前X个。因此，@ Brian McGinity是正确的。我仍然需要regexp方法来 而不是返回点“”。尽管。

Answer 1

如您所愿，完成100％sql后它将运行最快。

substr/instr是oracle中的 native 编译函数。

如果将其放在plsql函数中，由于在sql和plsql之间进行上下文切换，它的运行速度会变慢:

由于上下文切换，它的速度较慢:

select extrip( filename ) from million_row_table 

您所拥有的更快。

更新:

试试这个:

select s,
       substr(s,   nullif( instr(s,'.', -1) +1, 1) )
from ( 
     select 'no_extension_should_return_null' s from dual union
     select 'another.test.1'                    from dual union
     select 'another.test.doc'                  from dual union
     select 'another.test.docx'                 from dual union
     select 'another.test.stupidlng'            from dual 
     )