List:General Discussion« Previous MessageNext Message »
From:Vladimir Berezniker Date:April 30 1999 4:28pm
Subject:Re: Using LIKE with different languages
View as plain text  
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.

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