[TYPO3-dev] MySQL First Letter and Umlauts

[Steffen Kamper]

"Martin Kutschker" <martin.kutschker-n0spam at no5pam-blackbox.net> schrieb im 
Newsbeitrag 
news:mailman.1.1193417004.12261.typo3-dev at lists.netfielders.de...
> Steffen Kamper schrieb:
>> Hi,
>>
>> i need a tip for an alphabetic List in mySQL (all in utf8).
>>
>> I did this (for drwiki)
>> SELECT DISTINCT keyword,ASCII(UPPER(SUBSTRING(keyword,1,1))) letter FROM 
>> `tx_drwiki_pages` ORDER BY letter
>>
>> This works fine until the keyword starts with Umlaut, e.g. ÄÖÜ
>> The strange is that Ä results in ASCII 195, but it is 196 ...
>
> ASCII will return the numeric value of the first byte (not character), so 
> it'll return always 195 for all umlauts. The reason is that in utf8 
> multibyte characters start with a lead in that deontes the number of 
> characters. For two byte characters it's 195.
>
> Try
>
> SET NAMES utf8;
> select ascii('Ä');
> select ord('Ä');
> select ascii(convert('Ä' using latin1));
> select ord(convert('Ä' using latin1));
>
> check the results and have a look a the Mysql documentation.
>
> Masi
>

Ah, thx for opening my eyes! I get 196 now.
I read already the mySQL Doc - this is my standard reference - but 
multibyte-safe isn't clear with multibyte. Only a few Stringfunctions has a 
remark about - does that means that all where nothing is mentioned are not 
multibytesafe?

So last remaining is the order, if i try your suggested
SELECT DISTINCT keyword,ASCII(UPPER(LEFT(CONVERT(keyword USING latin1),1))) 
letter FROM
 `tx_drwiki_pages` ORDER BY letter COLLATE latin1_german2_ci

i get error "COLLATION 'latin1_german2_ci' is not valid for CHARACTER SET 
'binary' "

but now with

SELECT DISTINCT keyword,ASCII(UPPER(LEFT(CONVERT(keyword USING latin1),1))) 
letter FROM
 `tx_drwiki_pages` ORDER BY CONVERT(keyword USING latin1) COLLATE 
latin1_german2_ci

it seems to give me the correct results.I have to create some Pages "around" 
the Ä to proove.

thx again,

vg  Steffen