• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • Last Modified:

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?
0
sasha85
Asked:
sasha85
  • 8
  • 4
  • 3
4 Solutions
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 8
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now