Using Contains to Select for Literal Asterisk

[former_SQLA_member1694939]

I'm attempting to write a query in SQL Anywhere 17 that uses the CONTAINS function and searches for a literal asterisk. I've used all the escape sequences I could find and none are working, they all return the error that an asterisk is prefix search only, so it appears to not be escaping it as intended. I've tried

SELECT * FROM TransactionJournalDetail CONTAINS( TransactionJournalDetail.TLI_ReceiptAlias, '\\\\*');

SELECT * FROM TransactionJournalDetail CONTAINS( TransactionJournalDetail.TLI_ReceiptAlias, '\\*');

-and (somewhat desperately)-

SELECT * FROM TransactionJournalDetail CONTAINS( TransactionJournalDetail.TLI_ReceiptAlias, '[*]');

-and-

SELECT * FROM TransactionJournalDetail CONTAINS( TransactionJournalDetail.TLI_ReceiptAlias, '\\x2A');

which I thought would work as according to the documentation it sounds like I should be able to use the hex value, but it didn't work. When I use the hex value it does read it as an asterisk but still tosses the prefix error.

I've also tried:

SELECT * FROM TJ_TransactionJournalDetail CONTAINS( TJ_TransactionJournalDetail.TLI_ReceiptAlias, '\\\\x2A' );

but this isn't returning any results, even though I know there are asterisks in that field, so I'm not confident that it's actually searching for an asterisk and not some other character or combination of characters.

-and-

SELECT * FROM TJ_TransactionJournalDetail CONTAINS( TJ_TransactionJournalDetail.TLI_ReceiptAlias, '"*"' );

per Volker's suggestion, which returned the prefix search only error.

-also tried-

SELECT * FROM ecrs.TJ_TransactionJournalDetail CONTAINS( ecrs.TJ_TransactionJournalDetail.TLI_ReceiptAlias, UNISTR('\\u002a') );

and this also returns the prefix search only error.

None of them work! I don't know where else to go with this.

Any ideas on how to select for a literal asterisk using CONTAINS would be greatly appreciated, including any suggestions, leads or additional information if I'm misinterpreting how to escape the character.

I know I can use:

SELECT * FROM TransactionJournalDetail WHERE TransactionJournalDetail.TLI_ReceiptAlias LIKE '%*%';

however I'd prefer to use the optimized CONTAINS function if possible. It's so much faster!

Thank you!

[VolkerBarth]

What about using the asterisk as part of a phrase, i.e. enclosed in double quotes like

SELECT * FROM TJ_TransactionJournalDetail CONTAINS( TJ_TransactionJournalDetail.TLI_ReceiptAlias, '"*"' );

Note: This is a very wild guess as the docs do state the following (IMHO unclear phrase) for special characters in a phrase:

With the exception of asterisk, special characters are not interpreted as special characters when they are in a phrase.

That being said, the cited docs in your question relate to general string literals where the CONTAINS search condition has its own pecularities - just saying:)

[former_SQLA_member1694939]

Thanks, Volker! '"*"' did not work, I once again got the prefix search only error.

[former_SQLA_member1694939]

I updated the post to reflect that I tried that solution as well. Thanks again!

[former_SQLA_member1694939]

Maybe I should have linked the the CONTAINS docs. I did look at them, but I don't see any answer forthcoming there either.

[former_SQLA_member1694939]

If it isn't possible to search for an asterisk it would be nice if the docs stated it explicitly. As Volker said, there is a lot of ambiguous language surrounding the issue.

[VolkerBarth]

Just a further remark: As full text search is based on terms and only alphanumeric characters can build terms whereas all other characters are treated as "term breakers", in my limited understanding the asterisk as a non-alphanumeric character is treated as a term breaker and can therefore NEVER appear within a term - and as such cannot be searched for via full text search.

You can check this yourself by using the builtin sa_char_terms/sa_nchar_terms functions.

call sa_char_terms ('Hello *-% 123 world', 'default_char', 'sys');
-- returns (ignoring the asterisk and other non-alphanumier chars)
Hello,   1
123,     2
world,   3

---

I don't know if extern term breaker libraries would be able to change that behavior. Again, as stated, it's just what I do understand from the docs.

[former_SQLA_member1694939]

Thanks for the deeper explanation, Volker. I ran the function and got the same results. What you're saying makes sense, although I'm still hoping someone will waltz along with a solution that works that doesn't require using an external term breaker.