CODEWITHSUNDEEP
sqloracle-database
goldenbutter
goldenbutter

Reputation: 589

substring / extract expected from string value in oracle

Hi i have below text and taking filename from below text. but need date extract as well. but instead of hard coded i want as substring function where i can extract date from the text itself.

select 
SUBSTR(fname, INSTR(fname, '_', -1)+1) as fname,  
'20181217' as fdate 
from
(
   select '/home/dir/file_name_20181217_product.csv' as fname from dual
);

Upvotes: 0

Views: 170

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can always use regexp_substr():

   replace(regexp_substr(fname, '_[0-9]{8}_', 1, 1), '_', '') as date

And for the filename:

   regexp_substr(fname, '[^_]+$', 1, 1) as fname,

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

this will work:

select 
SUBSTR(fname, INSTR(fname, '/',1,3)+1),
instr(fname,'_')-INSTR(fname, '/',1,3)+1)) as fname,  
to_date(substr(fname,instr(fname,'_',1)+1,8),YYYYMMDD) as fdate 
from
(
   select '/home/dir/file_name_20181217_product.csv' as fname from dual
);

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Assuming that date always occurs before the first underscore( where your "fname" starts), and it's of 8 digits, you could do:

select 
SUBSTR(fname, INSTR(fname, '_', -1)+1) as fname,  
SUBSTR(fname, INSTR(fname, '_', -1)-8,8) as fdate
from
(
   select '/home/dir/file_name_20181217_product.csv' as fname from dual
);

FNAME       fdate    
----------- --------
product.csv 20181217

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

If you want to get date "as is", then this might help:

SQL> select
  2    SUBSTR(fname, INSTR(fname, '_', -1)+1) as fname,
  3    regexp_substr(fname, '\d+') as fdate                  --> this
  4  from (select '/home/dir/file_name_20181217_product.csv' as fname from dual);

FNAME       FDATE
----------- --------
product.csv 20181217

SQL>

Upvotes: 0

Related Questions