List:General Discussion« Previous MessageNext Message »
From:Giancarlo Bonansea Date:April 30 1999 4:45pm
Subject:RE: Using LIKE with different languages
View as plain text  
Hi Vladimir,

Thanks for your cooperation. With that solution the result would be the
expected, but the processing time will grow substantially :-( . And if the
word contains more than one character with accent the SELECT string will
grow either becoming more and more complex. IMHO, this functionality should
be implemented inside the sql server.

If the '=' operator can do this kind of comparison, why not doing the same
with the 'LIKE' operator? The code is already into the server!

Still waiting...

Giancarlo

-----Original Message-----
From: Vladimir Berezniker [mailto:volchok@stripped]
Sent: Friday, April 30, 1999 1:28 PM
To: Giancarlo Bonansea
Cc: mysql@stripped
Subject: Re: Using LIKE with different languages


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.

---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread2706@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.
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