1 2 Previous Next 19 Replies Latest reply: Nov 20, 2012 8:51 AM by Paul Horth Go to original post RSS
      • 15. Re: String matching
        916384
        Dear Paul,

        This is exactly right, sorry for not define properly.

        'PLEASE EXPLAIN CLEARLY'

        'PLEASE COULD YOU EXPLAIN CLEARLY'
        'EXPLAIN CLEARLY PLEASE'
        'CLEARLY I AM NOT EXPLAINING PLEASE'
        'EXPLAINPLEASECLEARLY'
        'AARGH! PLEASE! CLEARLY! BONGEXPLAINKRUMP'

        Regrads
        • 16. Re: String matching
          Paul  Horth
          fahedakhter wrote:
          Dear Paul,

          This is exactly right, sorry for not define properly.

          'PLEASE EXPLAIN CLEARLY'

          'PLEASE COULD YOU EXPLAIN CLEARLY'
          'EXPLAIN CLEARLY PLEASE'
          'CLEARLY I AM NOT EXPLAINING PLEASE'
          'EXPLAINPLEASECLEARLY'
          'AARGH! PLEASE! CLEARLY! BONGEXPLAINKRUMP'

          Regrads
          OK, this is modified from code posted by Alberto Faenza in thread {message:id=10682732}
          with wordstosearch as
           (select 'LOCAL BOPP' txt
              from dual)
          -- Create a table with one row for each word to search 
          ,
          wordtable as
           (select regexp_substr(txt
                                ,'\w+'
                                ,1
                                ,level) word
              from wordstosearch
            connect by level <= length(regexp_replace(txt
                                                     ,'(\s+)?\w+(\s+)?'
                                                     ,'x')))
          -- This is my table with comments
          ,
          comments as -- This is some sample data
           (select 'BOPP ROLL 175 MM (20 uM) (LOCAL)' cmt
              from dual
            union all
            select 'BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)' cmt
              from dual
            union all
            select 'LOCAL BOPP' cmt
              from dual
            union all
            select '(LOCAL) fgafgf (BOPPALONG)' cmt
              from dual
            union all
            select 'asdfd BOPP asdafs'
              from dual
            union all
            select 'afgaf LOCAL agaf'
              from dual
            union all
            select 'sadga hah '
              from dual)
          -- the final query
          select cmt
            from comments c
            join wordtable w
              on regexp_like (c.cmt, w.word)
           group by cmt
          having count (*) = (select count(*)
                                from wordtable);
          This should give you a clue how to write your code.
          • 17. Re: String matching
            chris227
            You might consider uising an text index like i show i this thread
            Re: Regular Expression matching all the given words
            In your case there might be no reason to use a custom stoplist.

            Edited by: chris227 on 20.11.2012 05:32
            • 18. Re: String matching
              kendenny
              Paul Horth wrote:
              fahedakhter wrote:
              Dear Paul,

              This is exactly right, sorry for not define properly.

              'PLEASE EXPLAIN CLEARLY'

              'PLEASE COULD YOU EXPLAIN CLEARLY'
              'EXPLAIN CLEARLY PLEASE'
              'CLEARLY I AM NOT EXPLAINING PLEASE'
              'EXPLAINPLEASECLEARLY'
              'AARGH! PLEASE! CLEARLY! BONGEXPLAINKRUMP'

              Regrads
              OK, this is modified from code posted by Alberto Faenza in thread {message:id=10682732}
              with wordstosearch as
              (select 'LOCAL BOPP' txt
              from dual)
              -- Create a table with one row for each word to search 
              ,
              wordtable as
              (select regexp_substr(txt
              ,'\w+'
              ,1
              ,level) word
              from wordstosearch
              connect by level <= length(regexp_replace(txt
              ,'(\s+)?\w+(\s+)?'
              ,'x')))
              -- This is my table with comments
              ,
              comments as -- This is some sample data
              (select 'BOPP ROLL 175 MM (20 uM) (LOCAL)' cmt
              from dual
              union all
              select 'BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)' cmt
              from dual
              union all
              select 'LOCAL BOPP' cmt
              from dual
              union all
              select '(LOCAL) fgafgf (BOPPALONG)' cmt
              from dual
              union all
              select 'asdfd BOPP asdafs'
              from dual
              union all
              select 'afgaf LOCAL agaf'
              from dual
              union all
              select 'sadga hah '
              from dual)
              -- the final query
              select cmt
              from comments c
              join wordtable w
              on regexp_like (c.cmt, w.word)
              group by cmt
              having count (*) = (select count(*)
              from wordtable);
              This should give you a clue how to write your code.
              Ooh. Nice. I was going to post a PL/SQL block that constructed a dynamic SQL string but yours is so much better.
              But there is a problem with your solution. If there's a cmt that has half the words in the input string and that same cmt occurs twice.
              • 19. Re: String matching
                Paul  Horth
                Thanks, but not really my code: I modified from Alberto's.

                Agreed about the problem having duplicates - well spotted!

                However the O/P can easily select distinct DESCRIPTIONS as I don't think that matters too much.

                In my (Alberto's) code:
                with wordstosearch as
                 (select 'LOCAL BOPP' txt
                    from dual)
                -- Create a table with one row for each word to search 
                ,
                wordtable as
                 (select regexp_substr(txt
                                      ,'\w+'
                                      ,1
                                      ,level) word
                    from wordstosearch
                  connect by level <= length(regexp_replace(txt
                                                           ,'(\s+)?\w+(\s+)?'
                                                           ,'x')))
                -- This is my table with comments
                ,
                comments as
                 (select distinct cmt
                    from (select 'BOPP ROLL 175 MM (20 uM) (LOCAL)' cmt
                            from dual
                          union all
                          select 'BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)' cmt
                            from dual
                          union all
                          select 'LOCAL BOPP' cmt
                            from dual
                          union all
                          select 'LOCAL BOPP'
                            from dual
                          union all
                          select '(LOCAL) fgafgf (BOPPALONG)' cmt
                            from dual
                          union all
                          select 'asdfd BOPP asdafs'
                            from dual
                          union all
                          select 'afgaf LOCAL agaf'
                            from dual
                          union all
                          select 'sadga hah '
                            from dual))
                -- the final query
                select distinct cmt --,count(*)
                  from comments c
                  join wordtable w
                    on regexp_like (c.cmt, w.word)
                 group by cmt
                having count (*) = (select count(*)
                                      from wordtable);
                1 2 Previous Next