substring / extract expected from string value in oracle

Question

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

You'll need to define a pattern that you can use - it looks like you have an 8-digit 'date', everything from the last slash up to that is the file name, for instance. And then look at the regexp_substr function, maybe? You can only stick with plain substr if the file name has a fixed number of underscores in those positions, really; which may be the case for your real names, of course... — Alex Poole
– Alex Poole, Commented Dec 17, 2018 at 16:24
sorry for confusion, i have edited the date output. i need exact same date what comes in the text from substring instring command — goldenbutter
– goldenbutter, Commented Dec 17, 2018 at 16:33
You are using technical terms for things that are different from their standard definition. In a field like computer technology, that may create problems. In your example, the entire substring file_name_20181217_product.csv is the filename. Your current code extracts just product.csv - that is not the filename, at least not according to the standard technical definition. You would do well to create a new term for what you need, so as not to cause confusion, both for yourself and for others who may work on the same task, now or in the future. — user5683823
– user5683823, Commented Dec 17, 2018 at 17:42

Kaushik Nayak · Accepted Answer · 2018-12-17 16:59:50Z

2

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

answered Dec 17, 2018 at 16:59

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

goldenbutter Over a year ago

product.csv is not the filename..... i have already substr for filename... which is working properly.. i needed extraction of date only.... which i think no instr required. this is fixed filename so this can be extracted by TO_DATE(SUBSTR(fname, 11,8),'DDMMYYYY') as fdate

Gordon Linoff · Accepted Answer · 2018-12-17 17:02:05Z

1

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,

answered Dec 17, 2018 at 17:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Littlefoot · Accepted Answer · 2018-12-17 16:55:30Z

0

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>

answered Dec 17, 2018 at 16:55

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Nikhil · Accepted Answer · 2018-12-17 17:00:35Z

0

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

answered Dec 17, 2018 at 17:00

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

Collectives™ on Stack Overflow

substring / extract expected from string value in oracle

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related