This discussion is archived
7 Replies Latest reply: Jul 5, 2013 7:31 PM by kevinUCB RSS

Use Escaped character  *  as wildcard chacater.

spur230 Newbie
Currently Being Moderated

I am using Oracle 11.2.0.3.

 

select * from TEXT_TBL where catsearch(srch_nm,'B\*BANK','')>0

 

gives me :

B*BANK MARKETS LLC

B*BANK LLC

 

However, select * from TEXT_TBL where catsearch(srch_nm,'B\*BANK*','')>0  does not yeild any records.  I think 2nd instance of * is also being escaped and not used as wildcard character. Is it possible to escape first instace of * and use second instace of * as reserved word.

 

Thank for you time.

  • 1. Re: Use Escaped character  *  as wildcard chacater.
    Herald ten Dam Expert
    Currently Being Moderated

    Hi,

     

    the wildcard in Oracle Text is not * but the %, so I think you have to rewrite the query to:

     

    select * from TEXT_TBL where catsearch(srch_nm,'B\*BANK%','')>0

     

    to do the trick.

     

    Herald ten Dam

    http://htendam.wordpress.com

  • 2. Re: Use Escaped character  *  as wildcard chacater.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    HeraldtenDam wrote:

    the wildcard in Oracle Text is not * but the %

    The wildcard for contains with a context index is %, but the wildcard for catsearch with a ctxcat index is *.  The following shows that you get correct results without setting the asterisk as a printjoin, but setting the asterisk as a printjoin yields incorrect results.

    SCOTT@orcl_11gR2> select * from v$version

      2  /

     

    BANNER

    --------------------------------------------------------------------------------

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

    PL/SQL Release 11.2.0.1.0 - Production

    CORE    11.2.0.1.0    Production

    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

     

    5 rows selected.

     

    SCOTT@orcl_11gR2> create table text_tbl

      2    (srch_nm  varchar2(30))

      3  /

     

    Table created.

     

    SCOTT@orcl_11gR2> insert all

      2  into text_tbl values ('B*BANK MARKETS LLC')

      3  into text_tbl values ('B*BANK LLC')

      4  into text_tbl values ('B*BANKtest LLC')

      5  into text_tbl values ('BaBANKtest LLC')

      6  into text_tbl values ('another name')

      7  select * from dual

      8  /

     

    5 rows created.

     

    SCOTT@orcl_11gR2> create index text_idx

      2  on text_tbl (srch_nm)

      3  indextype is ctxsys.ctxcat

      4  /

     

    Index created.

     

    SCOTT@orcl_11gR2> select dr$token from dr$text_idx$i

      2  /

     

    DR$TOKEN

    ----------------------------------------------------------------

    ANOTHER

    B

    B

    B

    BABANKTEST

    BANK

    BANK

    BANKTEST

    LLC

    LLC

    LLC

    LLC

    MARKETS

    NAME

     

    14 rows selected.

     

    SCOTT@orcl_11gR2> select * from TEXT_TBL

      2  where  catsearch (srch_nm, 'B\*BANK', '') > 0

      3  /

     

    SRCH_NM

    ------------------------------

    B*BANK MARKETS LLC

    B*BANK LLC

     

    2 rows selected.

     

    SCOTT@orcl_11gR2> select * from TEXT_TBL

      2  where  catsearch (srch_nm, 'B\*BANK*', '') > 0

      3  /

     

    SRCH_NM

    ------------------------------

    B*BANK MARKETS LLC

    B*BANK LLC

    B*BANKtest LLC

     

    3 rows selected.

     

    SCOTT@orcl_11gR2> drop index text_idx

      2  /

     

    Index dropped.

     

    SCOTT@orcl_11gR2> begin

      2    ctx_ddl.create_preference ('text_lexer', 'basic_lexer');

      3    ctx_ddl.set_attribute ('text_lexer', 'printjoins', '*');

      4  end;

      5  /

     

    PL/SQL procedure successfully completed.

     

    SCOTT@orcl_11gR2> create index text_idx

      2  on text_tbl (srch_nm)

      3  indextype is ctxsys.ctxcat

      4  parameters ('lexer text_lexer')

      5  /

     

    Index created.

     

    SCOTT@orcl_11gR2> select dr$token from dr$text_idx$i

      2  /

     

    DR$TOKEN

    ----------------------------------------------------------------

    ANOTHER

    B*BANK

    B*BANK

    B*BANKTEST

    BABANKTEST

    LLC

    LLC

    LLC

    LLC

    MARKETS

    NAME

     

    11 rows selected.

     

    SCOTT@orcl_11gR2> select * from TEXT_TBL

      2  where  catsearch (srch_nm, 'B\*BANK', '') > 0

      3  /

     

    SRCH_NM

    ------------------------------

    B*BANK MARKETS LLC

    B*BANK LLC

     

    2 rows selected.

     

    SCOTT@orcl_11gR2> select * from TEXT_TBL

      2  where  catsearch (srch_nm, 'B\*BANK*', '') > 0

      3  /

     

    no rows selected

     

  • 3. Re: Use Escaped character  *  as wildcard chacater.
    spur230 Newbie
    Currently Being Moderated

    Barbara -  I did not understand  why  catsearch is not returning any rows after creating lexer preference for the query below. I did not had * as printjoin but had some other lexer preference but still the query below did not return any rows.

    select * from TEXT_TBL  where  catsearch (srch_nm, 'B\*BANK*', '') > 0

     

     

    If I had * as printjoin and remove escape character , I seem to get correct answer.

    select * from TEXT_TBL  where  catsearch (srch_nm, 'B*BANK*', '') > 0

  • 4. Re: Use Escaped character  *  as wildcard chacater.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    I don't understand either.  I was just pointing out the differences.  Unless someone else can explain it, I am inclined to label it a bug, but I could be missing something.

  • 5. Re: Use Escaped character  *  as wildcard chacater.
    kevinUCB Explorer
    Currently Being Moderated

    Can you run the following test case:

     

    create table catfoo (bar varchar2(100));

    insert into catfoo values ('B*BANK MARKETS LLC');

    insert into catfoo values ('B*BANKS BOXING VIDEOS LLC');

    insert into catfoo values ('B*BANK LLC');

    insert into catfoo values ('B*BANKS');

    insert into catfoo values ('B*BANKtest');

    commit;

    create index tx_catfoo on catfoo(bar) indextype is ctxsys.ctxcat;

     

     

    SQL> select * from catfoo where catsearch(bar,'B\*BANK','')>0;

     

    BAR
    --------------------------------------------------------------------------------
    B*BANK MARKETS LLC
    B*BANK LLC

     

    SQL> select * from catfoo where catsearch(bar,'B\*BANK*','')>0;

     

    BAR

    --------------------------------------------------------------------------------

    B*BANK MARKETS LLC

    B*BANKS BOXING VIDEOS LLC

    B*BANK LLC

    B*BANKS

    B*BANKtest

     

    If this works properly for you (if I captured your requirements correctly), then can you show us a test case that incorporates your lexer preferences and doesn't work the same way as mine?

  • 6. Re: Use Escaped character  *  as wildcard chacater.
    spur230 Newbie
    Currently Being Moderated

    I do not have access to database now but could you please look at Barbara's 2nd example

    or add printjoin lexer preference to your CTXCAT index and

    try

    select * from catfoo where catsearch(bar,'B\*BANK*','')>0;

     

    From what I see, it is not returning any rows if CTXCAT index has printjoin lexer preference.


  • 7. Re: Use Escaped character  *  as wildcard chacater.
    kevinUCB Explorer
    Currently Being Moderated

    I thought you stated that you were not defining the * as a printjoin.

    Have you determined that this is part of your approach, and you now want to investigate the issue Barbara has pointed out? Or is there some other lexer preference that causes an issue for you?

    I agree that defining wildcards as printjoins leads to some "interesting" behavior.

    First, add a few new rows to illuminate:

    insert into catfoo values ('B* BANK NOTE THE SPACE AFTER THE *');

    insert into catfoo values ('B BANK WHERE DID THE * GO?');

    insert into catfoo values ('A LINE THAT DOESN''T START WITH B');

    insert into catfoo values ('A LINE THAT DOESN''T CONTAIN THE LETTER IN QUESTION');

    insert into catfoo values ('BUNDESBANK IN GERMANY');

    insert into catfoo values ('B BANKS WHERE DID THE * GO?');

    Now let's see what happens

    SQL> select * from catfoo where catsearch(bar,'{B*BANK}*','')>0;

     

    BAR

    --------------------------------------------------------------------------------

    B*BANK MARKETS LLC

    B*BANK LLC

    BUNDESBANK IN GERMANY

     

    SQL>  select * from catfoo where catsearch(bar,'B\*BANK*','')>0;

     

    BAR

    --------------------------------------------------------------------------------

    B BANK WHERE DID THE * GO?

    B BANKS WHERE DID THE * GO?

     

    SQL> select * from catfoo where catsearch(bar,'B\*BANK *','')>0;

     

    BAR

    --------------------------------------------------------------------------------

    B*BANK MARKETS LLC

    B*BANK LLC

     

    SQL> select * from catfoo where catsearch(bar,'B*BANK *','')>0;

     

    BAR

    --------------------------------------------------------------------------------

    B*BANK MARKETS LLC

    B*BANK LLC

    BUNDESBANK IN GERMANY

     

    SQL> select * from catfoo where catsearch(bar,'B*BANK*','')>0;

     

    BAR

    --------------------------------------------------------------------------------

    B*BANK MARKETS LLC

    B*BANKS BOXING VIDEOS LLC

    B*BANK LLC

    B*BANKS

    B*BANKtest

    BUNDESBANK IN GERMANY

     

    SQL> select * from catfoo where catsearch(bar,'B{*}BANK*','')>0;

     

    no rows selected


     

    SQL> select * from catfoo where catsearch(bar,'B{*} BANK*','')>0;

     

    BAR

    --------------------------------------------------------------------------------

    B BANK WHERE DID THE * GO?

    B BANKS WHERE DID THE * GO?

    So, I have to guess a bit at the behavior here; some of it is documented, but I'm filling in some gaps along the way.

    The parsing of the query seems to be:

    1. If you don't escape the *, it's still treated as a wildcard. So B*BANK matches BUNDESBANK, which is not what you were planning (right?), but is at least understandable, consistent behavior.

    2. If you escape the wildcard, the escaped * is treated as a literal and B*BANK matches only B*BANK (as you would expect).

    3. If you escape the first occurrence of the * but not the second inside the same token, the transformation is from 'B\*BANK*' to 'B{*}BANK*', which apparently gets expanded as 'B{*} BANK*' (see support note 1489635.1 at https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?id=1489635.1&type=DOCUMENT&displayIndex=1&returnToSrI…). I can't say that I'm convinced that the behavior is expected, but I suppose it is consistent. But it's even odder -- if the resultset was generated from 'B{*} BANK*', I would expect to see the row 'B* BANK NOTE THE SPACE AFTER THE *', and not the two rows that are returned. It appears that the expression is changed from 'B{*} BANK*' to 'B BANK*' -- not even 'B* BANK*', since that should return three rows. In any case, the root of the problem seems to be that you can't both escape the * and process the * as a wildcard within the same token. That's the test case I'd take to Support if you're planning to use * as a printjoin in your solution.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points