Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Unanswered: instr, substr, ......

    This is a quick question about oracle functions I had done this in the past but cannot find my code and am to lazy to redevelop

    I have

    select fieldname from table

    /lvl1/lvl2/lvl3/filename.txt
    /lvl1/lvl2/filename2.txt

    i want to pull out filename1 and filename2

    in a select substr(fieldname,instr(fieldname,'/')), length(fieldname) -4) from table

    type of statment but ,instr(fieldname,'/')) is not the correct start position.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would REVERSE the string, substring to the first '/', then re-REVERSE the output.

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Thank you MCrowley

    I came up with another solution I will try your suggestion always want to try new ways to skin a cat.

    select substr(fieldname,instr(fieldname,'/',-1)+1), length(fieldname)-substr(fieldname,instr(fieldname,'/',-1)) -4) from table

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    using the REVERSE function isn't necessary because INSTR has a "reverse" feature
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's another option:
    Code:
    SQL> select * From test;
    
    FN
    ------------------------------
    /lvl1/lvl2/lvl3/filename.txt
    /lvl1/lvl2/filename2.txt
    /lvl1/lvl2/lvl3/lvl4/l5/f3.txt
    /lvl1/file4.txt
    
    SQL> SELECT
      2    REPLACE(REGEXP_SUBSTR(fn, '[^/]+', INSTR(fn, '/', -1)), '.txt', '') result
      3  FROM TEST
      4  ;
    
    RESULT
    ------------------------------
    filename
    filename2
    f3
    file4
    
    SQL>
    P.S. Forgot to mention ... your code does nothing at the moment, rbackmann. Why?
    Code:
    SQL> select substr(fn,instr(fieldname,'/',-1)+1), length(fieldname)-substr(field
    name,instr(fieldname,'/',-1)) -4) from test
      2  ;
    select substr(fn,instr(fieldname,'/',-1)+1), length(fieldname)-substr(fieldname,
    instr(fieldname,'/',-1)) -4) from test
    
                               *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected
    
    
    SQL>

  6. #6
    Join Date
    May 2006
    Posts
    132
    Other option:

    Code:
    SQL> select  filename
      2     , regexp_substr(filename, '[^/]*$') ">= 10g"
      3     , substr(filename, instr(filename, '/', -1)+1) "< 10g"
      4  from t1;
    
    FILENAME                       >= 10g          < 10g
    ------------------------------ --------------- ---------------
    /lvl1/lvl2/lvl3/filename.txt   filename.txt    filename.txt
    /lvl1/lvl2/filename2.txt       filename2.txt   filename2.txt
    filename3.txt                  filename3.txt   filename3.txt

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Brian, you should remove '.txt' as well

  8. #8
    Join Date
    May 2006
    Posts
    132
    Ahhh man...requirements getcha everytime !

    Thanks Littlefoot, I completely overlooked that pertinent fact.

    Revised versions to accommodate for that.

    10g+ version:
    Code:
    SQL> select filename,
      2	regexp_replace(filename, '(.*/|^)([^.]*)(.*)', '\2') "Stripped" from t1;
    
    FILENAME                       Stripped
    ------------------------------ ---------------
    /lvl1/lvl2/lvl3/filename.txt   filename
    /lvl1/lvl2/filename2.txt       filename2
    filename3.txt                  filename3
    All versions:

    Code:
    SQL> select filename,
      2	substr(filename, instr(filename, '/', -1) + 1,
      3	instr(filename, '.', -1) - instr(filename, '/', -1) -1) "Stripped" from t1;
    
    FILENAME                       Stripped
    ------------------------------ ---------------
    /lvl1/lvl2/lvl3/filename.txt   filename
    /lvl1/lvl2/filename2.txt       filename2
    filename3.txt                  filename3

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Byouteafool!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •