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

Oracle: Fastest Way to Extract Filename Extension Using SQL or PL/SQL

提问by gfrobenius

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 nullreturned. 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 fastestsolution. Speed matters because this will end up being ran against a very large table. This is what I have so far...

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

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

那么有没有一种快速的方法来完成这个内联，还是我应该把它写在一个 PL/SQL 函数中？

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

UPDATEI'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.

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

UPDATEThanks for the answers so far. I ended up making a PL/SQL function that does what I need...

更新感谢您到目前为止的答案。我最终制作了一个可以满足我需要的 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;

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?

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

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

select extrip(filename) ext from testTable;

UPDATEI added a order by extto 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 NOTreturn the dot "." though.

更新我order by ext向这两个都添加了一个，然后重新运行测试，结果有所不同。正则表达式用了 9 秒，函数用了 17 秒。我想如果没有 TOAD 的命令，只是重新搜索第一个 X 数量的记录。所以@Brian McGinity 是对的。我仍然需要正则表达式方法来不返回点“。” 尽管。

回答by Brian McGinity

It will run fastest when done 100% sql, as you have.

就像您一样，当 100% sql 完成时，它将运行得最快。

The substr/instr are native compiled functions in oracle.

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

If you put this in a plsql function it will run slower due to context switching between sql and plsql:

如果你把它放在一个 plsql 函数中，由于 sql 和 plsql 之间的上下文切换，它会运行得更慢：

This is slower due to context switching:

由于上下文切换，这会变慢：

select extrip( filename ) from million_row_table 

What you have is faster.

你拥有的更快。

Update:

更新：

try this:

尝试这个：

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 
     )

回答by Tony B

You need to use regular expressions.

您需要使用正则表达式。

Try

尝试

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

I don't have an Oracle database to test this on but this should be pretty close.

我没有用于测试的 Oracle 数据库，但这应该非常接近。

Check the Oracle docs on regexp_substrand Using regular expressions in Oracle databasefor more info.

查看regexp_substr 上的Oracle 文档和在 Oracle 数据库中使用正则表达式以获取更多信息。

Update

更新

To drop the period from the file extension:

要从文件扩展名中删除句点：

select substr(regexp_substr(filename, '\.[^\.]*$'), 2)
from
    (select 'abc.def' filename from dual);

回答by ioudas

SELECT NULLIF(substr(filename,instr(filename,'.',-1)+1,length(filename)-instr(filename,'.',-1)) from (select 'no_extension_should_return_null' filename from dual) t1, SELECT filename from t1);

Sorry no oracle to test it, I'm sure you get the idea though.

抱歉没有 oracle 来测试它，但我相信你明白了。

回答by likhith

Yeah as per my understanding you can use DECODE function and query goes as follows:

是的，根据我的理解，您可以使用 DECODE 函数，查询如下：

SELECT substr(filename,instr(filename,'.',-1)+1,length(filename)- DECODE(INSTR(filename,'.',-1),0,LENGTH(filename),INSTR(filename,'.',-1))) from (select 'no_extension_should_return_null' filename from dual);

回答by idavid2013

Perhaps the simplest would be to use

也许最简单的方法是使用

regexp_substr(filename, '[^\.]*$')

regexp_substr(文件名, '[^\.]*$')

It works on filenames with multiple periods and returns no period.

它适用于具有多个句点的文件名，并且不返回句点。

---

---

For filenames without extension next could be used

对于没有扩展名的文件名 next 可以使用

select case when filename like '%.%' then regexp_substr(filename, '[^.]*$') end EXT from dual

select case when filename like '%.%' then regexp_substr(filename, '[^.]*$') end EXT from dual