1 2 Previous Next 15 Replies Latest reply: Nov 23, 2012 3:16 PM by newbie RSS

    finding reg exp values

    newbie
      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
          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
            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
              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
                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
                  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
                    Thanks Ranit

                    it is working fine but for finding 2 files taking 17 secs :(
                    • 7. Re: finding reg exp values
                      Frank Kulash
                      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
                        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
                          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
                            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-Oracle
                              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
                                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
                                  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
                                    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