Hi,
What if you create SQL like:
SELECT * FROM Cities WHERE name LIKE "Taly Bag_" AND SubString(Name, 9,1) IN
("e", "é").
Idea being that you replace any suspitiouse character with "_" so here we
have name LIKE "Taly Bag_". That should return all records that have all valid
characters in place. Second step is generating aditional conditions for the
where clause in form SubString(Name, 9,1) IN ("e", "é") for each "_" in the LIKE
part of the clause, and generating a list of valid characters for that possition
through the use of IN function.
So in general you can use SubString(Name, x, 1) IN ([list of valid
characters) and you should have as many of such parts as the "_" characters in
the [name LIKE "Taly Bag_"] part.
I appologize in advance if I am not very clear. I will gladly try to explain
any part that you might not understand.
Sincerely,
Vladimir Berezniker
Giancarlo Bonansea wrote:
> 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
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread2696@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
