UTF8 Order By

utf8 is universal encoding...but HOW CAN I ORDER BY it to diffrent languages alphabetic order?
every 2 utf8 chars is a letter in russia\hebrew\arabic...

when i do  Order by left(location,2)"
i got order of letters but not alfabetic...

what can i do?
sasha85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
I know this probably not what you were hoping for, but I've looked and looked for a way to sort directly in the query, and I can't find one that would work for this.  Maybe someone else will, but until then, here are some possible other ways to accomplish this.

1.  If you are using a language like php, you might find it much easier to do the sort in the language.  If you are accessing the entire table for your application, and it's not too big, you can retrieve it into an array and sort the array before processing.

2.  If that is not practical, then another possibility is to convert your letters into numerical values before inserting into the database.  Create another field for the sorting in which you put the numerical coding of the text that needs to be sorted.  You can do this conversion in php or whatever language you are using.

0
sasha85Author Commented:
oo my god...i realy hope there is better ways...
cause every uneveral languges sites uses utf8...i hope they are not working like that...
and i am using asp
0
Cornelia YoderArtistCommented:
I did find something here that might help you.  It has a few paragraphs on sorting that I don't fully understand myself, but perhaps you will ...

http://dev.mysql.com/tech-resources/articles/4.1/unicode.html
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Cornelia YoderArtistCommented:
And here's another one that written for php/mysql, but maybe you can find something helpful ...

http://www.onphp5.com/article/6
0
Cornelia YoderArtistCommented:
And one more that looks pretty negative on finding an easy solution ...

http://support.microsoft.com/kb/232580
0
sasha85Author Commented:
i read all...i realy hope i will find something else...cause i don't want to open special tables only for sorting..
0
NovaDenizenCommented:
In databases this sort of thing is called collation order.  Collation order is also usually defined by your locale (a collection of settings specifying language, date formats, decimal formats, and other things related to internationalization (i.e. non-English computing)

In a database, you usually set a collation order for a database or a table or a particular field, which the ORDER BY clause takes into account when it compares strings.
0
sasha85Author Commented:
do you know where i can find it in MYSQL Administrator?
0
NovaDenizenCommented:
I'm not real familiar with MySQL Administrator, but this looks like the place to configure the per-table collation order:  http://dev.mysql.com/doc/administrator/en/gui-table-editor-Table-Options.html

I searched around the documentation website a little for a per-column character set or collation order feature in MySQL Administrator, but I couldn't find any information about one.  If you want to do per-column collation ordering I think you'll have to bite the bullet and learn to administer MySQL from a command-line session.  That's a good idea anyway, to shed the training wheels of your GUI :) .

Reading the pages at  http://dev.mysql.com/doc/refman/5.1/en/charset.html would be a good idea.

To do per-column settings, you have to use a command like:
ALTER TABLE tablename CHANGE COLUMN fieldname fieldname VARCHAR(1234) CHARACTER SET utf8 COLLATION utf8_unicode_ci;
That would let MySQL know that tablename.fieldname is a utf8 string that should be sorted with the utf8_unicode_ci collation order.
0
sasha85Author Commented:
you mean "COLLATE"?
0
sasha85Author Commented:
cause if yoy do, i tried to set the hebrew version first:
i tested:
 utf8_swedish_ci
 utf8_general_ci

i have to say that this changes the order but again not to the right alphabetic hebrew order
0
NovaDenizenCommented:
Yep, COLLATE not COLLATION.

If utf8_general_ci doesn't do it for you then I don't know what do do.  The specific collation orders are for various languages that share characters but order them differently  (like Swedish and German).  Since Hebrew is the sole user of its characters, it's not necessary to have any collation orders other than the default one.

You can specify collation order in a lot of different ways, including with each query.  This would be useful in experimenting with different collation orders.
SELECT X FROM T ORDER BY X COLLATE collation_name;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sasha85Author Commented:
you mean
mysql="SELECT X FROM T ORDER BY X COLLATE hebrew_general_ci"
?
0
sasha85Author Commented:
cause that gives:
 icrosoft OLE DB Provider for ODBC Drivers error '80040e31'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.21-community-nt]COLLATION 'hebrew_general_ci' is not valid for CHARACTER SET 'utf8'
0
sasha85Author Commented:
and if i use other collates this still wrong as like from the mysql administrator...

is there an option to sort only by first letter and provide the list of letters inside the query?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.