Case Insensitive Full Text Search

[9727604]

Hi,

I am writing a search page that searches job postings and returns results based on a keyword entered. When the following query is run I get 4 results

SELECT p.postingId, p.jobTitle, p.industry, p.city, p.numPositions, p.status, p.ce, p.ae, p.cb, p.dateCreated, p.appDeadline, p.lastModified FROM postings AS p WHERE MATCH (p.jobTitle, p.division, p.industry, p.referenceNo, p.region, p.jobDescription, p.minimumEducation, p.mandatoryQualifications, p.additionalQualifications, p.otherInformation) AGAINST ('Coordinator' IN BOOLEAN MODE)

When this query is run I get 0 results
SELECT p.postingId, p.jobTitle, p.industry, p.city, p.numPositions, p.status, p.ce, p.ae, p.cb, p.dateCreated, p.appDeadline, p.lastModified FROM postings AS p WHERE MATCH (p.jobTitle, p.division, p.industry, p.referenceNo, p.region, p.jobDescription, p.minimumEducation, p.mandatoryQualifications, p.additionalQualifications, p.otherInformation) AGAINST ('coordinator' IN BOOLEAN MODE)

The only difference between the two is the keyword is in a different case. I've read the Full Text specifications and they say that the search should be insensitive. The collation on the postings table (the only table in the query) is utf8_general_ci and this should also mean case insensitive searches. I've been scratching my head at this for hours but can't figure it out, can anyone help me please?

Thanks in advance

 

[r937]

i'm not completely sure, but i think it's the IN BOOLEAN MODE that is causing the case sensitivity

try taking that part off

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[towerbase]

It would be helpful if you could specify which version of MySQL you are using, which storage engine and which operating system.

That way someone might be able to reproduce the symptoms of your problem.

I use Full Text searches quite a lot and have not experienced this problem.

Andrew