-
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!
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|