Utf-8 order by problem

[dushy]

hello all,

I have a table that in ms access has a "first_name" and "last_name" fields
all the data is in utf-8 encoding
when i try to make a query like:

SELECT * FROM myTable order by last_name;

I get all the rows but they are not ordered alphabetically
they are ordered by the utf-8 order.

how can i get the rows orderd alphabetically?

[MMcCarthy]

hello all,

I have a table that in ms access has a "first_name" and "last_name" fields
all the data is in utf-8 encoding
when i try to make a query like:

SELECT * FROM myTable order by last_name;

I get all the rows but they are not ordered alphabetically
they are ordered by the utf-8 order.

how can i get the rows orderd alphabetically?

Try this...

SELECT * FROM myTable order by last_name ORDER BY last_name;
 

Mary

[dushy]

do you mean that i need to put the order by 2 times???

i get error
Syntax error in ORDER BY clause

when i do
order by last_name ORDER BY last_name

what do you mean?

[MMcCarthy]

Sorry I was half asleep last night doing this. Ignore my previous post. I'll have to do some research on this and get back to you. In the meantime someone else may have the answer.

Mary

[NeoPa]

hello all,

I have a table that in ms access has a "first_name" and "last_name" fields
all the data is in utf-8 encoding
when i try to make a query like:

SELECT * FROM myTable order by last_name;

I get all the rows but they are not ordered alphabetically
they are ordered by the utf-8 order.

how can i get the rows orderd alphabetically?

I don't even get utf-8 encoding as an option :confused:
I would run an update query to change the stored data to the form you need.

[Killer42]

I don't even get utf-8 encoding as an option :confused:
I would run an update query to change the stored data to the form you need.

If the data is required in the current format, what about sorting on a calculated column which uses a user-defined function (or whatever) to convert to a more readily sorted format?

This is more of a last-resort workaround, and may only be an option if retrieving fairly small sets of data. But worth keeping in mind if all else fails.

[Denburt]

If you could provide me with more information about how this data was entered I might be able to help.

I found this and If I understand this correctly....
maybe it will help:

UTF-8

Right-to-left enabled Access supports several code pages used in importing, saving, and exporting HTML Web pages.

Code page conversion of tables or worksheets with right-to-left characteristics is provided for HTML files stored in logical (logical: Used to describe insertion point movement and text selection when working with bidirectional text. Movement progresses within bidirectional text according to the direction of the language encountered.) text layout and one of the following right-to-left code pages:
Language Code page
Arabic UTF-8 (default)
ISO 8859-6
DOS-720
ASMO-708
Hebrew UTF-8 (default)
ISO 8859-8
DOS-862

Note Access supports editing and saving of HTML files in logical text layout. HTML files stored in visual (visual: Used to describe insertion point movement and text selection when working with bidirectional text. Movement progresses within bidirectional text by moving to the next visually adjacent character.) text layout can be viewed but not edited or saved in logical text layout.

I suggest utilizing some HTML skills (determine the programming language used) and organize it in a manner more suited to your needs...

There are so many options.....

[Killer42]

I wonder also whether this may be relevant...

Specify the default language sort order (MDB)
Note The information in this topic applies only to a Microsoft Access database (.mdb).

1. On the Tools menu, click Options.
2. Click the General tab.
3. To specify the default language sort order for a new database, click the language that you want to use in the New database sort order box.

Note To reset the sort order for an existing database to the language that you selected in the General tab, compact that database.