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.

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.

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.

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 athttps://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.