Converting between character sets

[starritt@gmail.com]

If I have a column with the word Dürer in it can I use the Convert
function to convert it to Durer by down-shifting into a 7 bit character
set?! -- I can do this in Oracle. I am scripting with PHP and whenever
I use convert it seems to leave me with a situation where ü becomes a
question mark.

The database is latin1 by default -- if this isnt possible can anybody
think or point me toward a way of querying a column and bring back
records that might have Dürer or Durer only using Durer in my where
clause -- diacritic insensitivity essentially. Working in Oracle
great, but here I am stuck --

[Axel Schwenke]

[email]starritt@gmail.com[/email] wrote:

>
> If I have a column with the word Dürer in it can I use the Convert
> function to convert it to Durer by down-shifting into a 7 bit character
> set?! -- I can do this in Oracle.

When using MySQL you should stay away from Oracle "solutions" ;-)

> The database is latin1 by default -- if this isnt possible can anybody
> think or point me toward a way of querying a column and bring back
> records that might have Dürer or Durer only using Durer in my where
> clause -- diacritic insensitivity essentially.

If your MySQL version is 4.1 or above you can use MySQLs collation
support to achive diacritic insensitive string comparison.
(if your MySQL version is 4.0 or lower you should consider upgrading)

The latin1_german1_* collations work as you expect:

mysql> SELECT 'Dürer' = 'Durer' COLLATE latin1_german1_ci AS german1,
'Dürer' = 'Durer' COLLATE latin1_german2_ci AS german2;
+---------+---------+
| german1 | german2 |
+---------+---------+
| 1 | 0 |
+---------+---------+

The relevant chapter in the manual is:

[url]http://dev.mysql.com/doc/refman/5.0/en/charset.html[/url] -- especially
[url]http://dev.mysql.com/doc/refman/5.0/en/charset-we-sets.html[/url] -- and
[url]http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html[/url]


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]