Cyrillic Alphanumeric Ordering Problems

[RADical_Systems]

Hi,

I have a client in Russia using our software in Cyrillic and they are getting what I would describe as "odd" ordering of alphanumeric lists.

When displaying a list of names the order appears alomost random in that there will be a number of A's then a number of B's then again some A's, Some B's, Some C's, Some Z's then some A's again. For example if it was in English they get:

Aberthforth, Brian
Aimes, John,
Charles, A
Charleston, Peter
Avery, J
Bryan, Zoe

It might start off looking OK but then fails. I have a nice screenshot to show the example but I can't post this until I get to 100 points. If allowed I can post a link to the screenshot on our website if that helps.

I have tried running simple queries in ISQL and get the same results:

select customer.keyname from customer order by customer.keyname

They are using SQL Anywhere 10 on Windows 7 and XP.

It has been suggested that changing the collation of the database may help but this is totally new territory for me so thoughgt I would tap up the forum knowledge before I go down completely the wrong route.

Thanks in advance.

Alasdair

Added for Alasdair:

Character set 1252LATIN1, SQLA 10.0.1.3831

[VolkerBarth]

I'm no collation expert (but John is), but that seems like a collation problem.

Whereas changing a database's collation is a serious task (you have to rebuild it) and should only be done when the consequences are understood, you might simply try with the COMPARE() or SORTKEY functions whether different collations yield more appropriate results. When using the UCA collation and using 10.0.1 or above, you might also have a look at the so-called collation tailoring options.

Examples of such test queries:

select customer.keyname from customer
   order by sortkey(customer.keyname, '1251CYR');
select customer.keyname from customer
   order by sortkey(customer.keyname, 'UCA(locale=RU;case=respect)');

[RADical_Systems]

Thanks for the info Volker.

Just had the client try the statements and we do get different results for the first one but the ordering is still up the spout!

Justin may post the screenshots if he gets a chance and feels they're relevant.

I had a look at the collation tailoring options but I don't see anything other than your second example above that might help.

Thanks again.

[former_SQLA_member1694880]

It certainly is a collation problem. I've seen similar stuff when database has been created with 866CYR (OEM/DOS) collation but actual data were in 1251 code page.

[VolkerBarth]

Two things to note:

1. I just tried to give some samples of using SORTKEY but had not intent at all to suggest any particular collations or collation-tailoring strings, simply because I'm not familiar at all with Cyrillic or similar languages.

2. John is the expert, as stated:)