I'm using mysql 4 and full text searching. Currently there are problems with the searching resulting from special/accented characters in the database and inputted in the search.

A user should be able to enter either the 'special' version or the plain version and be returned both from the databse:

a or ä should match a and ä
é or e should match e and é
smart apostrophe should match smart apostrophe or '

etc...

currently entering special characters returns nothing or strange results and entering the plain versin only matches the plain version.


Does anyone have any suggestions?


Example of SQL currently in use:

SELECT * FROM table WHERE id != '' AND (MATCH title1 AGAINST ('ngä') OR MATCH title2 AGAINST ('ngä'))

Hello,

I haven't used MySQL full-text search, but I'm used to Oracle Text, and I think I understand your problem. I think you are facing two distinct issues :

- It is normal not to retrieve words with special characters (such as 'ä') when you try to replace these characters with standard ones (such as 'a' for 'ä'), because the indexing was only done on the word with 'ä', not the word with 'a'. MySQL may allow you to define rules such as 'ä'='a' for indexing, but I doubt so. I believe that you will have to replace programmatically special characters with standard ones BEFORE indexing and BEFORE querying, so that you never insert or query with special characters.

- If you get no result or strange results when querying with special characters, then I suppose you don't use the same character set when querying as the one you used when inserting (I speak of the client character set), hence a misinterpretation of special characters which are not converted the same way before being sent to the server.

I hope it will help you a little.

Regards,

RBARAER