MySQL Forums
Forum List  »  Full-Text Search

Changing case sensitivity in fulltext query
Posted by: beep beep
Date: February 07, 2006 10:11AM

Hi,

maybe it's plain simple, but I'm desperately stucked after reading all available documentation and googling around.

I would like to fulltext search my text fields in both case sensitive and case insensitive mode depending on user setting.

The mysql doc says:

"By default, the search is performed in case-insensitive fashion. In MySQL 4.1 and up, you can make a full-text search by using a binary collation for the indexed columns. For example, a column that has a character set of latin1 can be assigned a collation of latin1_bin to make it case sensitive for full-text searches."

So, where do I have to set and what collation to be able to search both case sensitive and insensitive manner? Can I set the appropriate collation at runtime? If I can, how?

What I would like is to have a query like this:

SELECT * FROM mytable WHERE MATCH(text_field) AGAINST('BANKING' IN BOOLEAN MODE);

In one mode I would like it to only match text_field-s that have exactly 'BANKING', and in the case insensitive mode I would like it to match 'BaNKinG' as well.

I thought making my text_field to have latin1_general_cs collation and then use for case insensitive searches a query like:

SELECT * FROM mytable WHERE MATCH(text_field) AGAINST('BANKING' IN BOOLEAN MODE) COLLATE latin1_general_ci;

This however resulted in a "ERROR 1253 (42000): COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'binary'".

Any help is highly appreciated!

Regards,
---
balazs

Options: ReplyQuote


Subject
Views
Written By
Posted
Changing case sensitivity in fulltext query
5928
February 07, 2006 10:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.