Oracle: SQL 또는 PL/SQL을 사용하여 파일 이름 확장명을 추출하는 가장 빠른 방법

Oracle: SQL 또는 PL/SQL을 사용하여 파일 이름 확장명을 추출하는 가장 빠른 방법

파일 이름의 확장자를 구해야 합니다.확장자는 길이(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

그렇다면 이를 인라인으로 수행하는 빠른 방법이 있습니까? 아니면 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;

그런 다음 2백만 행의 테이블에 대해 두 번의 테스트를 실행했습니다.설명 계획서를 보니 둘 다 100% 일치했습니다.그게 어떻게 가능하지?

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

select extrip(filename) ext from testTable;

업데이트 추가했습니다.order by ext두 사람 모두에게 검사를 다시 해봤는데 차이가 있었습니다.regexp는 9초, 기능은 17초가 걸렸습니다.TOAD의 주문 없이 처음 X개의 Rec를 재트렌딩 한 것 같습니다.그래서 @Brian McGini가 옳았습니다."." 점을 반환하지 않으려면 regexp 방법이 여전히 필요합니다.

당신이 그랬던 것처럼 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 
     )

규칙적인 표현을 사용해야 합니다.

해라

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

이것을 테스트할 Oracle 데이터베이스는 없지만 이 데이터베이스는 꽤 가까울 것입니다.

자세한 내용은 regexp_substrand의 Oracle 문서와 Oracle 데이터베이스의 정규식 사용을 확인하십시오.

갱신하다

파일 확장명에서 마침표를 삭제하려면:

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

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);

테스트하기에는 오라클이 없어서 죄송합니다. 그래도 이해하실 겁니다.

예, 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);

아마도 가장 간단한 방법은

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

여러 마침표가 있는 파일 이름에서 작동하고 마침표를 반환하지 않습니다.

---

확장명이 없는 파일 이름의 경우 next를 사용할 수 있습니다.

파일 이름이 '%'인 경우를 선택합니다.%' regexp_substr(파일명, '[^.*$')'는 이중에서 EXT를 종료합니다.

언급URL : https://stackoverflow.com/questions/21209750/oracle-fastest-way-to-extract-filename-extension-using-sql-or-pl-sql