7 Replies Latest reply: Apr 16, 2013 2:23 PM by Barbara Boehmer RSS

    Q: How to ignore operators inside CONTAINS ?

    mackrispi
      Hi,

      I use Context index for the search.

      The query :
      SELECT * FROM S_ARTICLE WHERE  CONTAINS(ORATEXT,'BT-50')>0 
      The problem is that BT is reserved word for Contains ....Broader Term ... and therefor I get

      ORA-20000: Oracle Text error:
      DRG-50901: text query parser syntax error on line 1, column 3

      I read that putting BT like {BT} would work .... but the problem is that I don't know what the user will type in ... query is created dynamicaly so I
      never know what is user typing I just put it inside CONTAINS ... and I would preffer to have it like this :)

      I tried to write queries like " CONTAINS(ORATEXT,'{BT-50 GOOD BREAD}' .... so putting the whole search phrase in brackets .... and it looks like it works ....

      So I would just like to get some confirmation from you guys, that this is actually correct ... if not then please correct me and please give me some example of how to do it better and correct.

      Thank you,
      Kris
        • 1. Re: Q: How to ignore operators inside CONTAINS ?
          Barbara Boehmer
          Putting the whole search phrase within { and } is a correct method. In addition to BT being a reserved word, the hyphen - is interpreted as a minus, so that if you search for BT-50, it subtracts the score for records containing 50 from the score for records containing BT. The other alternative is to escape each individual character with a \, like \BT\-50, but that requires some checking and replacing, which is more difficult than just enclosing the whole thing in { and }.
          • 2. Re: Q: How to ignore operators inside CONTAINS ?
            mackrispi
            Great. Thanks a lot Barbara.

            Regards,
            Kris
            • 3. Re: Q: How to ignore operators inside CONTAINS ?
              mackrispi
              Hi,

              Me again ... I just found out that now if I use {} .... I can't use wildcards ... like LONDO_ or LONDO% for LONDON ... cause {LONDO%} nor {LONDO}% is working ....

              What can I do ?

              Thank you,
              Kris
              • 4. Re: Q: How to ignore operators inside CONTAINS ?
                Barbara Boehmer
                Well, that brings you back to using \ to escape each potential problem. You can do this using REPLACE. You can either use nested REPLACEs in your query or for convenience create a function that either nests them or applies them separately. If you concatenate '%' to the end of each search string, then it eliminates problems with reserved words and you just have to deal with special characters. I have provided an example below, escaping just the hypen that you have identified as a problem and concatenating a wildcard, first within the query, then using a function.
                SCOTT@orcl_11gR2> CREATE TABLE s_article
                  2    (oratext  VARCHAR2(30))
                  3  /
                
                Table created.
                
                SCOTT@orcl_11gR2> INSERT ALL
                  2  INTO s_article VALUES ('BT-50')
                  3  INTO s_article VALUES ('BT-500')
                  4  INTO s_article VALUES ('BT-50 GOOD BREAD')
                  5  INTO s_article VALUES ('other data')
                  6  SELECT * FROM DUAL
                  7  /
                
                4 rows created.
                
                SCOTT@orcl_11gR2> CREATE INDEX test_idx ON s_article (oratext)
                  2  INDEXTYPE IS CTXSYS.CONTEXT
                  3  /
                
                Index created.
                
                SCOTT@orcl_11gR2> VARIABLE search_string VARCHAR2(100)
                SCOTT@orcl_11gR2> EXEC :search_string := 'BT-50'
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> SELECT * FROM S_ARTICLE
                  2  WHERE  CONTAINS
                  3             (ORATEXT,
                  4              REPLACE (:search_string, '-', '\-') || '%') > 0
                  5  /
                
                ORATEXT
                ------------------------------
                BT-50
                BT-500
                BT-50 GOOD BREAD
                
                3 rows selected.
                
                SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION format_search_string
                  2    (p_search_string IN VARCHAR2)
                  3    RETURN             VARCHAR2
                  4  AS
                  5    v_search_string        VARCHAR2(32767) := p_search_string;
                  6  BEGIN
                  7    v_search_string := REPLACE (v_search_string, '-', '\-') || '%';
                  8    RETURN v_search_string;
                  9  END format_search_string;
                 10  /
                
                Function created.
                
                SCOTT@orcl_11gR2> SHOW ERRORS
                No errors.
                SCOTT@orcl_11gR2> SELECT * FROM S_ARTICLE
                  2  WHERE  CONTAINS
                  3             (ORATEXT,
                  4              format_search_string (:search_string)) > 0
                  5  /
                
                ORATEXT
                ------------------------------
                BT-50
                BT-500
                BT-50 GOOD BREAD
                
                3 rows selected.
                • 5. Re: Q: How to ignore operators inside CONTAINS ?
                  mackrispi
                  ok great, so if i escape -+ these two characters I should be good to go , hopefully :)

                  thank you for showing me the right direction.

                  Best regards,
                  Kris
                  • 6. Re: Q: How to ignore operators inside CONTAINS ?
                    581530
                    Dear Sir,
                    I am having similar type of Problem.
                    I have a CLOb column which stores PLSQL data.
                    for Example select * from Departments
                    When I run Query
                    SELECT SQL ,CONTAINS(SQL, 'SELECT \* FROM' || '%') t6t FROM HR.ORAEXT_REPOSITORY WHERE CONTAINS(SQL, 'SELECT \* FROM' || '%') > 1
                    no result is returned but when i run
                    SELECT SQL ,CONTAINS(SQL, 'SELECT \* FROM' || '%') t6t FROM HR.ORAEXT_REPOSITORY WHERE CONTAINS(SQL, 'SELECT \* FROM' ) > 1
                    rows starting with (select * from) are returned.

                    Please advice me about this issue

                    Thanks
                    • 7. Re: Q: How to ignore operators inside CONTAINS ?
                      Barbara Boehmer
                      In the future, please start a new thread, instead of hijacking an old thread. If you need to reference an old thread, you can copy and paste a link to it. Only the original poster can mark a thread as answered and this one has already been so marked.

                      Since "from" is a default stopword, you will need to either use an empty_stoplist or a stoplist that does not contain that word.

                      You do not need to use "%" to search for all rows containing the phrase 'select * from'. If you use 'select \* from' || '%', you are telling it to search for any row that contains the 'select * ' followed by any word that begins with 'from'. Oracle Text searches are not like using 'like' in sql in that you do not need to include an ending '%' and the phrase may be anywhere within the text, not just at the beginning of the phrase.

                      Also, you should just use score instead of repeating the contains clause in the select clause.

                      Please see the demonstration below.
                      SCOTT@orcl_11gR2> CREATE TABLE oratext_repository
                        2    (sql  CLOB)
                        3  /
                      
                      Table created.
                      
                      SCOTT@orcl_11gR2> INSERT ALL
                        2  INTO oratext_repository VALUES ('SELECT * FROM dept')
                        3  INTO oratext_repository VALUES ('SELECT * FROM emp')
                        4  INTO oratext_repository VALUES ('SELECT * FROMinvalidsql')
                        5  INTO oratext_repository VALUES ('I want to SELECT * FROM some table')
                        6  INTO oratext_repository VALUES ('This is SELECT * FROMAGE *')
                        7  SELECT * FROM DUAL
                        8  /
                      
                      5 rows created.
                      
                      SCOTT@orcl_11gR2> CREATE INDEX your_idx
                        2  ON oratext_repository (sql)
                        3  INDEXTYPE IS CTXSYS.CONTEXT
                        4  PARAMETERS ('STOPLIST CTXSYS.EMPTY_STOPLIST')
                        5  /
                      
                      Index created.
                      
                      SCOTT@orcl_11gR2> COLUMN sql FORMAT A40
                      SCOTT@orcl_11gR2> SELECT sql, SCORE(1)
                        2  FROM   oratext_repository
                        3  WHERE  CONTAINS (sql, 'SELECT \* FROM', 1) > 1
                        4  /
                      
                      SQL                                        SCORE(1)
                      ---------------------------------------- ----------
                      SELECT * FROM dept                                4
                      SELECT * FROM emp                                 4
                      I want to SELECT * FROM some table                4
                      
                      3 rows selected.
                      
                      SCOTT@orcl_11gR2> SELECT sql, SCORE(1)
                        2  FROM   oratext_repository
                        3  WHERE  CONTAINS (sql, 'SELECT \* FROM' || '%', 1) > 1
                        4  /
                      
                      SQL                                        SCORE(1)
                      ---------------------------------------- ----------
                      SELECT * FROM dept                                3
                      SELECT * FROM emp                                 3
                      SELECT * FROMinvalidsql                           3
                      I want to SELECT * FROM some table                3
                      This is SELECT * FROMAGE *                        3
                      
                      5 rows selected.