From: Giancarlo Bonansea Date: April 30 1999 4:45pm Subject: RE: Using LIKE with different languages List-Archive: http://lists.mysql.com/mysql/2707 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 '=3D' 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", "=E9"). 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", "=E9") 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=E9". > > But the problem is that the comparison must be done using the '=3D' 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 '=3D' 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=E9| > +---+---------+ > > Now let's try some SELECT statements: > > (1) SELECT * FROM cities WHERE name=3D'Taly Bage' -> Return 2 rows = (OK) > (2) SELECT * FROM cities WHERE name=3D'Taly Bag=E9' -> Return 2 rows = (OK) > (3) SELECT * FROM cities WHERE name=3D'TALY BAGE' -> Return 2 rows = (OK) > (4) SELECT * FROM cities WHERE name=3D'TALY BAG=C9' -> 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=E9%' -> Return 1 row = "Taly > Bag=E9" (WRONG!) > (7) SELECT * FROM cities WHERE name LIKE '%e%' -> Return 1 row "Taly = Bage" > (WRONG!) > (8) SELECT * FROM cities WHERE name LIKE '%=E9%' -> Return 1 row = "Taly Bag=E9" > (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.