Forum Stats

  • 3,757,567 Users
  • 2,251,246 Discussions
  • 7,869,867 Comments

Discussions

REGEXP_SUBSTR question

John O'Toole
John O'Toole Member Posts: 645
edited Sep 14, 2010 3:52AM in SQL & PL/SQL
Folks, I'm trying to use REGEXP_SUBSTR to find the piece of text between the second last and last occurrence of a backslash. I can find the text between any specified occurrences of backslashes when I start at the front, but I don't know how many backslashes there will be in the string.
I'm on 10g R2, so don't have access to REGEXP_COUNT.
WITH test_data AS (
SELECT 'c:\temp\folderA\fileA.txt' t FROM DUAL UNION ALL
SELECT 'c:\temp\fileA.txt' t FROM DUAL UNION ALL
SELECT '\\mymachine\A\fileB.txt' t FROM DUAL UNION ALL
SELECT '\\mymachine\A\B\fileB.txt' t FROM DUAL UNION ALL
SELECT '\\mymachine\A\B\C\image.jpg' t FROM DUAL UNION ALL
SELECT '\\mymachine\A\B\C\D\music.mpg' t FROM DUAL UNION ALL
SELECT 'c:\myfolder\folderD\folderE\4969-A.txt' t FROM DUAL
)
SELECT t, REGEXP_SUBSTR(t,'[^\]+', 1, 3) my_string
FROM test_data;
The REGEXP_SUBSTR in that example gives me the third occurrence, which isn't quite right. The output I would like is:
folderA
temp
A
B
C
D
folderE
Many Thanks.

Best Answer

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Accepted Answer
    Without regexp
    SQL> WITH test_data AS (
      2  SELECT 'c:\temp\folderA\fileA.txt' t FROM DUAL UNION ALL
      3  SELECT 'c:\temp\fileA.txt' t FROM DUAL UNION ALL
      4  SELECT '\\mymachine\A\fileB.txt' t FROM DUAL UNION ALL
      5  SELECT '\\mymachine\A\B\fileB.txt' t FROM DUAL UNION ALL
      6  SELECT '\\mymachine\A\B\C\image.jpg' t FROM DUAL UNION ALL
      7  SELECT '\\mymachine\A\B\C\D\music.mpg' t FROM DUAL UNION ALL
      8  SELECT 'c:\myfolder\folderD\folderE\4969-A.txt' t FROM DUAL
      9  )
     10  SELECT t, substr(t, instr(t,'\',-1,2)+1, (instr(t,'\',-1,1)-instr(t,'\',-1,2))-1) my_string
     11  FROM test_data;
    
    T                                      MY_STRING
    -------------------------------------- --------------------------------------
    c:\temp\folderA\fileA.txt              folderA
    c:\temp\fileA.txt                      temp
    \\mymachine\A\fileB.txt                A
    \\mymachine\A\B\fileB.txt              B
    \\mymachine\A\B\C\image.jpg            C
    \\mymachine\A\B\C\D\music.mpg          D
    c:\myfolder\folderD\folderE\4969-A.txt folderE

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Accepted Answer
    Without regexp
    SQL> WITH test_data AS (
      2  SELECT 'c:\temp\folderA\fileA.txt' t FROM DUAL UNION ALL
      3  SELECT 'c:\temp\fileA.txt' t FROM DUAL UNION ALL
      4  SELECT '\\mymachine\A\fileB.txt' t FROM DUAL UNION ALL
      5  SELECT '\\mymachine\A\B\fileB.txt' t FROM DUAL UNION ALL
      6  SELECT '\\mymachine\A\B\C\image.jpg' t FROM DUAL UNION ALL
      7  SELECT '\\mymachine\A\B\C\D\music.mpg' t FROM DUAL UNION ALL
      8  SELECT 'c:\myfolder\folderD\folderE\4969-A.txt' t FROM DUAL
      9  )
     10  SELECT t, substr(t, instr(t,'\',-1,2)+1, (instr(t,'\',-1,1)-instr(t,'\',-1,2))-1) my_string
     11  FROM test_data;
    
    T                                      MY_STRING
    -------------------------------------- --------------------------------------
    c:\temp\folderA\fileA.txt              folderA
    c:\temp\fileA.txt                      temp
    \\mymachine\A\fileB.txt                A
    \\mymachine\A\B\fileB.txt              B
    \\mymachine\A\B\C\image.jpg            C
    \\mymachine\A\B\C\D\music.mpg          D
    c:\myfolder\folderD\folderE\4969-A.txt folderE
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    Easier to use REGEXP_REPLACE to remove everything you don't want...
    SQL> ed
    Wrote file afiedt.buf
    
      1  WITH test_data AS (
      2  SELECT 'c:\temp\folderA\fileA.txt' t FROM DUAL UNION ALL
      3  SELECT 'c:\temp\fileA.txt' t FROM DUAL UNION ALL
      4  SELECT '\\mymachine\A\fileB.txt' t FROM DUAL UNION ALL
      5  SELECT '\\mymachine\A\B\fileB.txt' t FROM DUAL UNION ALL
      6  SELECT '\\mymachine\A\B\C\image.jpg' t FROM DUAL UNION ALL
      7  SELECT '\\mymachine\A\B\C\D\music.mpg' t FROM DUAL UNION ALL
      8  SELECT 'c:\myfolder\folderD\folderE\4969-A.txt' t FROM DUAL
      9  )
     10  select regexp_replace(t, '^.*[\]([^\]*)[\][^\]*$','\1')
     11* from test_data
    SQL> /
    
    REGEXP_REPLACE(T,'^.*[\]([^\]*)[\][^\]*$','\1')
    ------------------------------------------------------------------
    folderA
    temp
    A
    B
    C
    D
    folderE
    
    7 rows selected.
    
    SQL>
    BluShadow
  • John O'Toole
    John O'Toole Member Posts: 645
    Perfect. With all this REGEXP lark around, its sometimes easy to forget about the old skool string functions.
    Thanks
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    col str for a20
    
    WITH test_data AS (
    SELECT 'c:\temp\folderA\fileA.txt' t FROM DUAL UNION ALL
    SELECT 'c:\temp\fileA.txt' t FROM DUAL UNION ALL
    SELECT '\\mymachine\A\fileB.txt' t FROM DUAL UNION ALL
    SELECT '\\mymachine\A\B\fileB.txt' t FROM DUAL UNION ALL
    SELECT '\\mymachine\A\B\C\image.jpg' t FROM DUAL UNION ALL
    SELECT '\\mymachine\A\B\C\D\music.mpg' t FROM DUAL UNION ALL
    SELECT 'c:\myfolder\folderD\folderE\4969-A.txt' t FROM DUAL)
    select t,RegExp_replace(t,'^.*?([^\]+)\\[^\]+$','\1') as str
      from test_data
    order by t;
    
    T                                       STR
    --------------------------------------  -------
    \\mymachine\A\B\C\D\music.mpg           D
    \\mymachine\A\B\C\image.jpg             C
    \\mymachine\A\B\fileB.txt               B
    \\mymachine\A\fileB.txt                 A
    c:\myfolder\folderD\folderE\4969-A.txt  folderE
    c:\temp\fileA.txt                       temp
    c:\temp\folderA\fileA.txt               folderA
    And I used regexp_count B-)
    select t,RegExp_substr(t,'[^\]+',1,-1+regexp_count(t,'[^\]+')) as str
      from test_data
    order by t;
    Aketi Jyuuzou
  • LostWorld
    LostWorld Member Posts: 239
    How do you people learn all this? where from you get these requirements and how do you find out which all functions you can apply at what scenario? Most of these oracle fucntions we don't use normally.


    Amzing i must say :)
  • Kanish
    Kanish Member Posts: 767
    It is really amazing, Could you explain me step by step.

    kanish
This discussion has been closed.