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:
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.
Added for Alasdair:
Character set 1252LATIN1, SQLA 10.0.1.3831
I'd say that it looks like someone has disabled (or otherwise bypassed) character set translation. This doesn't appear to be an NCHAR column since the first byte as reported in the "ascii()" column would not be reasonable lead bytes for Cyrillic characters. If it is a CHAR column, well, Cyrillic characters are not representable in CP1252. The characters 192 through 195 in CP1252 are accented forms of capital-A and are therefore considered equal: they will appear in an arbitrary order.
Wrong collation for Russian, should be 1251CYR instead.
answered 25 Aug '11, 05:10
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)');
Images from Alasdair of the results of using SORTKEY in the ordering as suggested in Volker's post:
answered 25 Aug '11, 05:24