CODEWITHSUNDEEP
mysqlfull-text-search
Flo
Flo

Reputation: 1671

Mysql Search LIKE with optional french characters

I have names in the database like

tést

The Autocomplete works fine and all and if I seach for it like this:

WHERE title LIKE '%tèst%'

It works fine. But now i also want to find this entry if the user enters

test

(without the accent). In most cases its fine as autocomplete fixes the user up, but of course not always. How can I change the query so that the needle "test" also finds "tést" (other way round does not matter)?

Upvotes: 0

Views: 1832

Answers (3)

Ike Walker
Ike Walker

Reputation: 65587

This works for me.

I tested this on MySQL 5.5.8 using utf8 charset and utf8_general_ci collation.

I tried searching for where title like '%tèst%' and where title like '%test%', and both queries returned both "tèst" and "test", which seems like what you want.

Does it not work like this for you?

mysql> create table french (
    -> title text
    -> ) default charset = utf8, default collate = utf8_general_ci;
Query OK, 0 rows affected (0.13 sec)

mysql> 
mysql> insert into french values ('tèst');
Query OK, 1 row affected (0.00 sec)

mysql> insert into french values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into french values ('toast');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from french where title like '%tèst%';
+-------+
| title |
+-------+
| tèst  |
| test  |
+-------+
2 rows in set (0.00 sec)

mysql> select * from french where title like '%test%';
+-------+
| title |
+-------+
| tèst  |
| test  |
+-------+
2 rows in set (0.00 sec)

Upvotes: 1

jmichalicek
jmichalicek

Reputation: 2416

You may be able to solve this with collation. Collations in MySQL determine how characters are compared to each other. The "e" and "è" difference is actually the example that MySQL uses in their documentation. What are your current character set and collation set to? I'm far from an expert on this, but may be able to help point you in the right direction still as to which collation to use.

Upvotes: 0

Silver Light
Silver Light

Reputation: 45972

Since MySQL search realization is very-very poor, there is no way to do it using just MySQL. By the way, you are not using full text searching (as mentioned in tags).

I would put this logic in your application, so it would find all the variants for those cases and append them to query.

A query you get at the end might look like this:

WHERE title LIKE '%tèst%' OR title LIKE '%test%'

Upvotes: 0

Related Questions