-
sql functions to trim absolute path to display just filename
SQL> select filename from binfileobject ;
FILENAME
--------------------------------------------------------------------------------
/var/tmp/ClientPer26692.tmp
/var/tmp/ClientPer26693.tmp
/var/tmp/ClientPer50108.tmp
/var/tmp/xyz/ClientPer26694.tmp
/var/tmp/abcdef/ClientPer26691.tmp
/var/tmp/hgdfjhagd/ClientPer7363216104934551917.tmp
can you let us know sql function to display it as
~~~~~~~~~~~~~~~~~
ClientPer26692.tmp
ClientPer26693.tmp
ClientPer50108.tmp
ClientPer26694.tmp
ClientPer26691.tmp
ClientPer7363216104934551917.tmp
something like ltrim , replace '/%/'
siva prakash
DBA
-
You can do it this way in perl script.
#!/usr/bin/perl
################################################################################
# Author: Malay Biswal
# Date : 2009-08-25
#
###################
use strict;
use constant LOOKUP => "D:/perl/perl_data/file.txt";
my $line;my@x;my $x;my $len;my $len;my $name;
MAIN: {
open(IN, "<".LOOKUP) || die("$0 - ERROR: Cannot open ".LOOKUP.": $!");
$line=0;
while($x=) {
$line++;
print "X IS: $x \n";
@x=split(/\//,$x);
#print "CALCULATING variable \n";
#$gc1=trim($x[0]); $gc2=trim($x[1]);
$len=$#x;
$name=$x[$len];
print "NAME: $name \n";
}
print "TOTAL LINE: $line \n";
close(IN);
}
-
try it then
work backwards to last \ and then get all text from it
-
Be happy, today is your lucky day...
Code:
SQL>
SQL> select substr('/var/tmp/ClientPer26692.tmp',(instr('/var/tmp/ClientPer26692.tmp','/',1,3)+1),length('/var/tmp/ClientPer26692.tmp')) "FileName"
2 from dual;
FileName
------------------
ClientPer26692.tmp
SQL>
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I dare to generalize it a bit (according to davey23uk's proposal):
Code:
SQL> select substr('/var/tmp/ClientPer26692.tmp',(instr('/var/tmp/ClientPer26692.tmp','/',-1)+1)) "FileName" from dual;
FileName
------------------
ClientPer26692.tmp
Ales The whole difference between a little boy and an adult man is the price of toys
-
Thank you all very much and ales.
select filename , substr(filename , (instr( filename , '/' , -1 ) +1) , length(filename)) "filename" from binfile ;
FILENAME filename
------------------------------------- -------------------------------------
/var/tmp/ClientPer26692.tmp ClientPer26692.tmp
/var/tmp/ClientPer26693.tmp ClientPer26693.tmp
/var/tmp/ClientPer50108.tmp ClientPer50108.tmp
/var/tmp/ClientPer26694.tmp ClientPer26694.tmp
/var/tmp/ClientPer26691.tmp ClientPer26691.tmp
/var/tmp/ClientPer7363216104934551917 ClientPer7363216104934551917.tmp
.tmp
/xxx/rrrr/aaaa/dddd/asdjfhgj.jpg asdjfhgj.jpg
/xxx/rrrr/aaa/djdjdj/adjfhadfh/jhasdg ddd.aa
fkj/ddd.aa
siva prakash
DBA
-
If you are on 10g+, you could consider regexp:
Code:
SQL> select regexp_substr('/var/tmp/ClientPer26692.tmp','[^/]*$') from dual;
REGEXP_SUBSTR('/VA
------------------
ClientPer26692.tmp
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|