DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: sql functions to trim absolute path to display just filename

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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);
    }
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    try it then

    work backwards to last \ and then get all text from it

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  7. #7
    Join Date
    Apr 2006
    Posts
    377
    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
  •  


Click Here to Expand Forum to Full Width