DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: CONTAINS with special symbols

  1. #1
    Join Date
    May 2011
    Posts
    2

    CONTAINS with special symbols

    I have the following problem:
    In this program full text search is activated using the one of the functions by Oracle Contains.
    As it is known, there are special symbols for this function, but i have to use (to search by) these special symbols like (by) regular ones.
    For example, you can see me in my text such symbols as - ` ~ # ^ | ' < > \ {} []+
    I tried to google it, and found out that these special symbols should be written(quoted, shielded) this way: {special symbol} But for some reason, it didn't work.
    If I use CONTAINS with regular symbols (not special ones), Contains shows me the grade the way it supposed to (or the right way)., but when I try to find (or search for) special symbols ( I tried to use one line full of special symbols for the test), Contains returns grades which are higher then zero for lines without special symbols
    and for the line containing only special symbols Zero.
    why is it happening this way and not the other way around?
    p.s. I am trying to update indexes!

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Would you mind in posting offending code?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2011
    Posts
    2
    Problem solved, here's an example
    SCOTT@10gXE> SELECT banner FROM v$version
    2 /

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

    SCOTT@10gXE> CREATE TABLE test_tab (test_col VARCHAR2 (30))
    2 /

    Table created.

    SCOTT@10gXE> SET DEFINE OFF SCAN OFF
    SCOTT@10gXE> INSERT ALL
    2 INTO test_tab VALUES ('print~join')
    3 INTO test_tab VALUES ('print!join')
    4 INTO test_tab VALUES ('print@join')
    5 INTO test_tab VALUES ('print$join')
    6 INTO test_tab VALUES ('print%join')
    7 INTO test_tab VALUES ('print^join')
    8 INTO test_tab VALUES ('print&join')
    9 INTO test_tab VALUES ('print*join')
    10 INTO test_tab VALUES ('print(join')
    11 INTO test_tab VALUES ('print)join')
    12 INTO test_tab VALUES ('print-join')
    13 INTO test_tab VALUES ('print_join')
    14 INTO test_tab VALUES ('print=join')
    15 INTO test_tab VALUES ('print+join')
    16 INTO test_tab VALUES ('print|join')
    17 INTO test_tab VALUES ('print;join')
    18 INTO test_tab VALUES ('print:join')
    19 INTO test_tab VALUES ('print,join')
    20 INTO test_tab VALUES ('print"join')
    21 INTO test_tab VALUES ('print 22 INTO test_tab VALUES ('print>join')
    23 INTO test_tab VALUES ('print.join')
    24 INTO test_tab VALUES ('print/join')
    25 SELECT * FROM DUAL
    26 /

    23 rows created.

    SCOTT@10gXE> EXEC CTX_DDL.CREATE_PREFERENCE ('my_lexer', 'BASIC_LEXER')

    PL/SQL procedure successfully completed.

    SCOTT@10gXE> EXEC CTX_DDL.SET_ATTRIBUTE ('my_lexer', 'PRINTJOINS', '~!@$%^&*()-_=+|;:,"./')

    PL/SQL procedure successfully completed.

    SCOTT@10gXE> CREATE INDEX test_idx ON test_tab (test_col)
    2 INDEXTYPE IS CTXSYS.CONTEXT
    3 PARAMETERS ('LEXER my_lexer')
    4 /

    Index created.

    SCOTT@10gXE> SELECT token_text FROM dr$test_idx$i
    2 /

    TOKEN_TEXT
    --------------------------------------------------------------------------------
    PRINT!JOIN
    PRINT"JOIN
    PRINT$JOIN
    PRINT%JOIN
    PRINT&JOIN
    PRINT(JOIN
    PRINT)JOIN
    PRINT*JOIN
    PRINT+JOIN
    PRINT,JOIN
    PRINT-JOIN
    PRINT.JOIN
    PRINT/JOIN
    PRINT:JOIN
    PRINT;JOIN
    PRINT PRINT=JOIN
    PRINT>JOIN
    PRINT@JOIN
    PRINT^JOIN
    PRINT_JOIN
    PRINT|JOIN
    PRINT~JOIN

    23 rows selected.

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'print@join') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print@join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'print^join') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print^join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'print:join') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print:join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'print"join') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print"join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'print 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print
    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'print.join') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print.join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'print/join') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print/join

    SCOTT@10gXE>
    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print~join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print~join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print!join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print!join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print$join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print$join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print%join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print%join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print&join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print&join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print*join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print*join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print(join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print(join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print)join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print)join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print-join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print-join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print_join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print_join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print=join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print=join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print|join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print|join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print;join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print;join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print,join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print,join

    SCOTT@10gXE> SELECT * FROM test_tab WHERE CONTAINS (test_col, '{print>join}') > 0
    2 /

    TEST_COL
    --------------------------------------------------------------------------------
    print>join

    SCOTT@10gXE>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width