This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 23, 2012 1:16 PM by newbie RSS

finding reg exp values

newbie Explorer
Currently Being Moderated
Hi folks pls help on this

dir_text is a column in the table abc_tab.

it is having the values for example 'c/documents/new/doc1' and 'd/documents/old/doc2' ...

select dir_text from abc_tab;

dir_text
----------------
c/documents/new/doc1
d/documents/old/doc2

select * from abc_tab where dir_text in ('doc1','doc2' ) -- here i should get the details.

if am writing
select * from abc_tab where dir_like '%doc1%' -- then i able to get result

i want to get last values after last '/' means doc1 and doc2

Please help on this
  • 1. Re: finding reg exp values
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    newbie wrote:
    Hi folks pls help on this

    dir_text is a column in the table abc_tab.

    it is having the values for example 'c/documents/new/doc1' and 'd/documents/old/doc2' ...

    select dir_text from abc_tab;

    dir_text
    ----------------
    c/documents/new/doc1
    d/documents/old/doc2

    select * from abc_tab where dir_text in ('doc1','doc2' ) -- here i should get the details.

    if am writing
    select * from abc_tab where dir_like '%doc1%' -- then i able to get result

    i want to get last values after last '/' means doc1 and doc2

    Please help on this
    If you want to find rows that end with any of the given targets:
    WITH     targets          AS
    (
         SELECT     'doc1'     AS target FROM dual UNION ALL
         SELECT     'doc2'               FROM dual
    )
    SELECT  SUBSTR ( dir_text
                , 1 + INSTR ( dir_text
                                  , '/'
                      , -1
                      )
                )          AS last_part
    FROM     abc
    WHERE     EXISTS
         (
              SELECT  1
              FROM     targets  
              WHERE     abc.dir_text  LIKE '%/'
                                || target
         )
    ;
    Regular expressions are very powerful, but you have to pay for that power whether you need it or not. In this case, it's easy enough to get the results you want without regular expressions.
  • 2. Re: finding reg exp values
    ranit B Expert
    Currently Being Moderated
    Try this...
    with xx as (
      select 'c/documents/new/doc1' txt from dual union all 
      select 'd/documents/old/doc2' from dual
    )
    select substr(txt,instr(txt,'/',-1,1)+1,length(txt)) from xx;
    Indeed Regular Exps are powerful but are costly. Only should be used when the requirement is that complex.
    IN this case, can be easily done by string functions..

    Please rectify me if i'm wrong.

    Ranit B.
  • 3. Re: finding reg exp values
    ranit B Expert
    Currently Being Moderated
    i want to get last values after last '/' means doc1 and doc2
    If you want to find rows that end with any of the given targets:
    WITH     targets          AS
    (
         SELECT     'doc1'     AS target FROM dual UNION ALL
         SELECT     'doc2'               FROM dual
    )
    SELECT  SUBSTR ( dir_text
                , 1 + INSTR ( dir_text
                                  , '/'
                      , -1
                      )
                )          AS last_part
    FROM     abc
    WHERE     EXISTS
         (
              SELECT  1
              FROM     targets  
              WHERE     abc.dir_text  LIKE '%/'
                                || target
         )
    ;
    Hi Frank,
    I guess you didn't get OP properly.
    Requirement is to find the string after the last '/' and not necessarily any target string is searched for.

    This is my understandng. Please let me know if you think otherwise.
    Ranit B.
  • 4. Re: finding reg exp values
    newbie Explorer
    Currently Being Moderated
    Hi Frank

    Many thanks for your quick response.

    Actually its a large db and the column has many values like unix directory folders path.
    like '<dir>/<folder>/<folder>/<filename>' or '<dir>/<folder>/<filename>' or any path.

    What i want to achieve is to get the file names after last '/' symbol,

    if am writing like

    select * from abc_tab where dir_text like '%<filename>%' --then i will get the result

    but if i write like this

    select * from abc_tab where dir_text in ('filename1','filename2',...) -- here i will give only file names then i should get the result

    Hope this explain clear.

    Please help on this query.

    Edited by: newbie on Nov 21, 2012 12:19 PM
  • 5. Re: finding reg exp values
    ranit B Expert
    Currently Being Moderated
    This works --
    with xx as (
      select 'c/documents/new/doc1' txt from dual union all 
      select '<dir>/<folder>/<folder>/<filename>' txt from dual union all 
      select 'd/documents/old/doc2' from dual
      
    )
    select substr(txt,instr(txt,'/',-1,1)+1,length(txt)) from xx;
    gives
    doc1
    <filename>
    doc2
    Edited by: ranit B on Nov 22, 2012 1:51 AM
    -- o/p added
  • 6. Re: finding reg exp values
    newbie Explorer
    Currently Being Moderated
    Thanks Ranit

    it is working fine but for finding 2 files taking 17 secs :(
  • 7. Re: finding reg exp values
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    newbie wrote:
    ... Hope this explain clear.
    Sorry, no.


    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data. Point out where the query I posted earlier is giving the wrong results, and explain how you get the right results in thiose places.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 8. Re: finding reg exp values
    ranit B Expert
    Currently Being Moderated
    for me it worked in a second.
    Some other pblm might be, but would be faster than regex.

    Try Disconnect & again reconnecting the session.

    Edited by: ranit B on Nov 22, 2012 2:08 AM
  • 9. Re: finding reg exp values
    newbie Explorer
    Currently Being Moderated
    Sorry Frank

    here you may get the clear picture :)
    with test as
    (
    select 'file:/folder1/file1' as filepath from dual
    union all
    select 'file:/folder1/folder2/file2' from dual
    union all
    select 'file:/folder1/folder2/folder3/file3' from dual
    union all
    select 'file:/folder1/folder2/folder3/file4' from dual
    )
    select * from  test where filepath like '%file1%' ---Here i able to get the result that is fine
    
    What i want to achieve is 
    
    select * from  test where filepath in ('file1','file2','file3') -- here am passing the file names directly but not able to get result
    PS: I have tried Ranit solution it worked but taking 17secs when 2 files only passed :(
    select * from  test where substr(filepath,instr(filepath,'/',-1,1)+1,length(filepath))  in ('file1','file2','file3')
    Added Ranit's solution at the bottom
  • 10. Re: finding reg exp values
    Solomon Yakobson Guru
    Currently Being Moderated
    newbie wrote:
    Added Ranit's solution at the bottom
    Ranit's solution is as fast as it can get. The only way to make it faster I am aware of is FBI (function based index). Issue:
    CREATE INDEX your_index_name
      ON your_table_name(
                         substr(filepath,instr(filepath,'/',-1) + 1)
                        )
    /
    Then issue:
    SELECT  *
      FROM  your_table_name
      WHERE substr(filepath,instr(filepath,'/',-1) + 1) in ('file1','file2','file3')
    /
    Check execution plan to verify index was used.

    SY.
  • 11. Re: finding reg exp values
    Nicosa Expert
    Currently Being Moderated
    Solomon Yakobson wrote:
    Ranit's solution is as fast as it can get. The only way to make it faster I am aware of is FBI (function based index). Issue:
    Or maybe Index on virtual column if version > 11g(R2?) :
    alter table test add (fname varchar2(140) generated always as (substr(filepath,instr(filepath,'/',-1,1)+1)) virtual);
    
    create indextest_fname_idx on test(fname);
    
    select filepath from test where fname in ('file1','file2','file3');
  • 12. Re: finding reg exp values
    ranit B Expert
    Currently Being Moderated
    Hi Solomon,

    But in my system it took 2 secs to fetch the result.

    Oracle Version - 11.0.2 XE
    Windows OS
    2Gb RAM

    Any idea why for 'Newbie' it is taking 17secs?

    Ranit B.
  • 13. Re: finding reg exp values
    ranit B Expert
    Currently Being Moderated
    Hi Newbie,

    What is suggest is, can you please try varying the count of rows and check how much tym each takes?
    Check with 1 row & then gradually increase the count.

    Lets try to figure out the exact behavior.

    Ranit B.
  • 14. Re: finding reg exp values
    chris227 Guru
    Currently Being Moderated
    You might take oracle text index into consideration:
    create table file_paths as
    (select 'file:/folder1/file1' as filepath from dual
    union all
    select 'file:/folder1/folder2/file2' from dual
    union all
    select 'file:/folder1/folder2/folder3/file3' from dual
    union all
    select 'file:/folder1/folder2/folder3/file4' from dual
    )
    
    CREATE INDEX  idx_file_paths_ctx ON  file_paths (filepath) 
       INDEXTYPE IS "CTXSYS"."CONTEXT"
    
    select
    *
    from
    file_paths
    where
    contains (filepath, 'file2 OR file3') > 0
    
    FILEPATH 
    file:/folder1/folder2/file2 
    file:/folder1/folder2/folder3/file3 
    
    With this you are alos able to search for pathes
    
    select
    *
    from
    file_paths
    where
    contains (filepath, 'folder3') > 0
    
    FILEPATH 
    file:/folder1/folder2/folder3/file3 
    file:/folder1/folder2/folder3/file4 
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points