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!