Geek Answers Handbook

Oracle: the fastest way to extract a file name extension using SQL or PL / SQL

I need to get file name extensions. Extensions can be of any length (not only 3), and they can also be absent, in which case I need to return null. I know that I could easily write a PL / SQL function that does this, and then just call that function in a query, but I was hoping I could somehow make it all inline. And I don't care how long this decision is, what I need is a quick decision. Speed ​​matters because it ends up running on a very large table. This is what I still have ...

/*
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 quick way to do this inline or just write it in a PL / SQL function?

This is what I work 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 , , , , , , .

UPDATE . 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;

UPDATE a order by ext , , . 9 , 17 . , TOAD X . , @Brian McGinity . regexp NOT "." .

+4
5

, 100% sql, .

/instr - .

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

.

Try

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

Oracle, , .

Oracle docs regexp_substr Oracle .

Update

:

select substr(regexp_substr(filename, '\.[^\.]*$'), 2)
from
    (select 'abc.def' filename from dual);
+3
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);

, , , , .

+2

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

,

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

.

select the case when the file name is "%.%", then regexp_substr (file name, "[^.] * $") end EXT from double

-1
source

More articles:


All Articles