List:General Discussion« Previous MessageNext Message »
From:Giancarlo Bonansea Date:April 30 1999 2:31pm
Subject:Using LIKE with different languages
View as plain text  
Hello,

I'm from Brazil and started using MySQL 3.22.21 configured with the Latin-1
character set. Everything works fine, including matching of characters with
accent, like the words "Bage" and "Bagé".

But the problem is that the comparison must be done using the '=' operator,
so only the complete match of the word is acceptable. Brazilians do not use
characters with accent as they should have to, so it's common to mispell the
words, and it's very important for the efectiveness of the application that
this problem could be handled.

The point is: the 'LIKE' operator behaves differently, talking about sort
ordering and matching, from the '=' operator. This is described in the
MySQL's manual. Is there a workaround for this limitation? Trying to be more
explicit, look at the sample described below.

Think about a table like the one below:

Table: cities
+---+---------+
|cod|  name   |
+---+---------+
| 1 |Taly Bage|
| 2 |Taly Bagé|
+---+---------+

Now let's try some SELECT statements:

(1) SELECT * FROM cities WHERE name='Taly Bage' -> Return 2 rows (OK)
(2) SELECT * FROM cities WHERE name='Taly Bagé' -> Return 2 rows (OK)
(3) SELECT * FROM cities WHERE name='TALY BAGE' -> Return 2 rows (OK)
(4) SELECT * FROM cities WHERE name='TALY BAGÉ' -> Return 2 rows (OK)
(5) SELECT * FROM cities WHERE name LIKE '%Bage%' -> Return 1 row "Taly
Bage" (WRONG!)
(6) SELECT * FROM cities WHERE name LIKE '%Bagé%' -> Return 1 row "Taly
Bagé" (WRONG!)
(7) SELECT * FROM cities WHERE name LIKE '%e%' -> Return 1 row "Taly Bage"
(WRONG!)
(8) SELECT * FROM cities WHERE name LIKE '%é%' -> Return 1 row "Taly
Bagé"
(WRONG!)

This is a important feature that I think, is missing in MySQL. I also work
with MS-SQL Server and it does not have this problem.

Any tip or workaround ?

Thanks in advance,

Giancarlo Bonansea
gbonanse@stripped
Thread
Using LIKE with different languagesGiancarlo Bonansea30 Apr
  • Re: Using LIKE with different languagesVladimir Berezniker30 Apr
  • Using LIKE with different languagesMichael Widenius2 May
RE: Using LIKE with different languagesGiancarlo Bonansea30 Apr
Re: Using LIKE with different languagesFernando Martins6 May