1. my installation:
[EMAIL PROTECTED]:~> uname -a Linux rosetta 2.4.19-4GB #1 Fri Sep 13 13:14:56 UTC 2002 i686 unknown [EMAIL PROTECTED]:~>
mysql> show global variables like "version"; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | version | 4.0.12-max-log | +---------------+----------------+ 1 row in set (0.00 sec)
mysql>
mysql> show global variables like "character_set"; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | character_set | latin1 | +---------------+--------+ 1 row in set (0.00 sec)
mysql> show global variables like "character_sets";
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
2. my problem:
when i am looking for entries with a name containing an u-umlaut by means of the LIKE construct, i get all chunk result-rows:
mysql> select name from contacts where name like "%ü%" order by name; +---------------------------------------------------------------+ | name | +---------------------------------------------------------------+ | A Côte beauty treats | | AA Anonyme Alkoholiker | | AA Anonyme Alkoholiker | | AAA All Activities Agency AG | | Adèle Uldry | | Adolf Deyhle | | Alain Barbey | | Alain et Dorothée Ryser | | Alain et Thérèse Sauty | | Alex et Silvia Fritschy | | Alexandre Stransky | ... ... | Yves Moullet | | Yves Zehfus | | Yves-André et Jennifer Cornioley | | Yves-François Weill | | Yves-Pierre Poscia | | Yvonne Badel | | Yvonne Frédérique Maeder | | Yvonne Malka | | Yvonne Quartenoud | | Zekarias Araya | | Zero Gravity | | Zürich | +---------------------------------------------------------------+ 485 rows in set (0.02 sec)
mysql>
if i, however, do the search with a regexp, it seams to work:
mysql> select name from contacts where name regexp "ü" order by name; +---------------------------------------------------------------+ | name | +---------------------------------------------------------------+ | Alfred Zürcher | | Alig Grossküchen | | Alois Kühner | | Ambulante Spitex Hausbetreuungsdienst für Stadt und Land | | Andreas Bühlmann | | Anton Bühlmann | | Arnd Küchel | | Aurélia et Yann Füllemann | | Bachblütenberatung | | Bahnhofleitung, Fundbüro, | | Batigroup AG Graubünden | | Beratungsbüro f. Personalmanagement | | Berghüsli | | Beyeler AG Drogerie-Parfümerie | | Brigitte Schüller | | Büchi und Müller AG Beratende Geologen u. Ingenieure SIA/USIC | .... .... | Sprachen fürs Leben | | Süesswinkel AG | | Thildy Müller | | Tomül | | Treuhandbüro Joachim Schweiger | | Ulrich Rudolph Rothenbühler | | Verkehrsbüro | | Vincent et Nootwadee Würsten | | Walter Blüchert | | Zürich | +---------------------------------------------------------------+ 88 rows in set (0.03 sec)
mysql>
in my opinion, the result of the two searches above should be exactly the same. i have found, however, in the docu:
REGEXP and RLIKE use the current character set (ISO-8859-1 Latin1 by default) when deciding the type of a character.
does the LIKE comparison not do that? i.e. is the LIKE comparison bound to pure US-ASCII or to whatever else?
BTW, i have done the above SELECTs not only with u-umlaut but also with a-umlaut, e-accent-grave, e-accent-aigu, a-accent-grave. the results are quite similar to the above example.
3. what am i missing?
thanks for your attention. with the best wishes for X-mas
suomi
-- ---------------------------------------- Ayni AG Sternenstrasse 24 P.O.Box CH-8027 Zurich Switzerland, Europe +41 1 280 22 44, Fax +41 1 280 22 49 E-mail: [EMAIL PROTECTED] Web: http://www.ayni.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
