I'm facing an issue with a MySQL fulltext index when trying to perform a case-insensitive search. I have a large column in my database where data is stored, and I want to be able to search for text regardless of case. For instance, if my data contains the text "be biopharma," I expect the search for "Be" to return this row, but it's not working as intended.
I attempted to perform a case-insensitive search using a fulltext index in MySQL. I used the following SQL query:
SELECT * FROM product WHERE MATCH(product.name) AGAINST ("+be*" IN BOOLEAN MODE);
I expected this query to return rows that contain the text "be biopharma" when I search for "Be." However, the query did not return the expected results. This issue arises because MySQL's fulltext search, by default, is case-sensitive. I'm seeking guidance on how to modify the query or configure the fulltext index to correctly perform a case-insensitive search.
If you want to search for 2-character words using full-text check this documentation. you have to do configuration based on the storage engine you use.
Along with that your search "Be" is a stopword check this documentation.