Forum Stats

  • 3,854,620 Users
  • 2,264,392 Discussions
  • 7,905,743 Comments

Discussions

How to set my Contains index to ignore operator sign when searching?

DannyS-Oracle
DannyS-Oracle Member Posts: 165
edited Feb 28, 2018 1:42PM in Text

Hi everyone,

I am a beginner in Oracle Text, please bear with me if I asked simple questions I have tried reading the Docs, but there is too much information to digest, and not straightforward enough to solve my problem.

I have a Context index, and I have set its lexer to identify '-' (hyphen) as a printjoint. In addition, I have marked '#' (hashtag) as a startjoin. But when I am searching using this query:

select *from   TABLE_NAMEwhere  contains(MULTI_SEARCH_COL, '#tag-name-30',1)>0

It does not return any result, even though I have one record which has '#tag-name-30' keyword included in its content. If I am using the escape character {}, it works:

select *from   TABLE_NAMEwhere  contains(MULTI_SEARCH_COL, '{#tag-name-30}',1)>0

returning 1 result.

My question is, is there a way to configure my index, such that the above pattern can be detected by default without using the escape operator? I am trying to expose a search field to my users, which will query items to the DB using Oracle Text, and I think my users should not need to learn about query operators to use it.

FYI, I am using OracleDB v12. Any feedback will be appreciated, thank you in advance!

Tagged:

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Feb 15, 2018 1:46AM Answer ✓

    Using {} escapes ALL the characters between the curly brackets.

    If you want to always escape everything that users search on, do it as part of you app...

    User puts in '#the-string' and you modify the variable by adding curly brackets to the string and pass it out to your query you fire off to the db, so db gets'{#the-string}'.

    No need to mess with the "current" index you have doing that...

    Although I would prefer to teach users how to use the search mechanism I introduced by explaining what the meta characters are and how to escape them either individually with '\' preceding the char or, as in your example, escape the whole string with '{}'.

       '{'||your_search_var'||'}'

    Another way would be to use concatenation or REPLACE(REPLACE()) to add a backslash to meta characters in the actual query.

    For example, here's those two methods being used in the select to show you the  transposition. You would use the function calls in your where clause:

    SQL> edWrote file afiedt.buf  1  WITH table_name (multi_search_col) AS (  2     SELECT '#tag-name-30' FROM dual  3  )  4  select '{'||multi_search_col||'}'                               curlybraces,  5         REPLACE(REPLACE(multi_search_col, '#', '\#'), '-', '\-') backslash  6* from   [email protected]:SQL+> /CURLYBRACES    BACKSLASH-------------- ---------------{#tag-name-30} \#tag\-name\-301 row selected.

    (Shame you didn't include create table, insert scripts and index creation, if you had then "we" could have done some testing on other options you may have).

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Feb 15, 2018 1:46AM Answer ✓

    Using {} escapes ALL the characters between the curly brackets.

    If you want to always escape everything that users search on, do it as part of you app...

    User puts in '#the-string' and you modify the variable by adding curly brackets to the string and pass it out to your query you fire off to the db, so db gets'{#the-string}'.

    No need to mess with the "current" index you have doing that...

    Although I would prefer to teach users how to use the search mechanism I introduced by explaining what the meta characters are and how to escape them either individually with '\' preceding the char or, as in your example, escape the whole string with '{}'.

       '{'||your_search_var'||'}'

    Another way would be to use concatenation or REPLACE(REPLACE()) to add a backslash to meta characters in the actual query.

    For example, here's those two methods being used in the select to show you the  transposition. You would use the function calls in your where clause:

    SQL> edWrote file afiedt.buf  1  WITH table_name (multi_search_col) AS (  2     SELECT '#tag-name-30' FROM dual  3  )  4  select '{'||multi_search_col||'}'                               curlybraces,  5         REPLACE(REPLACE(multi_search_col, '#', '\#'), '-', '\-') backslash  6* from   [email protected]:SQL+> /CURLYBRACES    BACKSLASH-------------- ---------------{#tag-name-30} \#tag\-name\-301 row selected.

    (Shame you didn't include create table, insert scripts and index creation, if you had then "we" could have done some testing on other options you may have).

This discussion has been closed.