[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

extracting a string using substr function

I have the following string in a variable, i want to extract myfile from the variable.

/dir1/dir2/myfile.042611_020650

output should be myfile, so i need to truncate everything around the string myfile
0
gram77
Asked:
gram77
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
gram77Author Commented:
select substr('/dir1/dir2/myfile.042611_020650',to_number(instr('/dir1/dir2/myfile.042611_020650','/', -
1))+1,/*need instr here*/) from dual;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this :

select substr('/dir1/dir2/myfile.042611_020650',instr('/dir1/dir2/myfile.042611_020650','/',1,3)+1,
instr('/dir1/dir2/myfile.042611_020650','.',1,1)-instr('/dir1/dir2/myfile.042611_020650','/',1,3)-1) from dual;
0
 
gram77Author Commented:
directory depth can be any
/dir1/dir2/myfile.042611_020650 may be
/dir1/dir2/dir3/myfile.042611_020650 or
/dir1/dir2/dir3/dir4/myfile.042611_020650
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pratima PharandeCommented:
try this
will work for all depth

select substr('/dir1/dir2/dir3/myfile.042611_020650',instr('/dir1/dir2/dir3/myfile.042611_020650','/',-1)+1,
instr('/dir1/dir2/dir3/myfile.042611_020650','.',1)-instr('/dir1/dir2/dir3/myfile.042611_020650' ,'/',-1)-1) from dual;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this :
select substr('/dir1/dir2/dir3/myfile.042611_020650',instr('/dir1/dir2/dir3/myfile.042611_020650','/',-1)+1,
instr('/dir1/dir2/dir3/myfile.042611_020650','.',1)-instr('/dir1/dir2/dir3/myfile.042611_020650' ,'/',-1)-1) from dual;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
previous post was not there when i opened this page and hence posted. i think both are same.
0
 
slightwv (䄆 Netminder) Commented:
Try regexp_replace:

select regexp_replace(regexp_replace('/dir1/dir2/myfile.042611_020650','(.*/)*',''),'\..*','')
from dual;

0
 
slightwv (䄆 Netminder) Commented:
Even easier:

select regexp_replace('/dir1/dir2/myfile.042611_020650','((.*/)*)(.*)(\..*)','\3')
from dual;

0
 
Amitkumar PSr. ConsultantCommented:
try the following also.

select substr('/dir1/dir2/myfile.042611_020650', instr('/dir1/dir2/myfile.042611_020650', 'myfile', 1), length('myfile'))  from dual;
0
 
slightwv (䄆 Netminder) Commented:
amit_n,

I believe the intent is to use ANY file name so you cannot hard code 'myfile'.
0
 
Amitkumar PSr. ConsultantCommented:
Thanks slightwv.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now