asked on

Sorting different sorts of characters in 8.0.5. ???

"Here's the deal: with public services at city hall here in Rotterdam, netherlands, there is an application with Delphi 3 and Oracle 8.0.5. (standard character set) using BDE (Borland Database Engine) connectivity. Now there are alot of people with special names. People from turkey, france, germany, etc. Alot of these people have these names starting with <for instance> o (with two dots on top) or a c (with the little tail at the bottom of the character). Anyway the database is configured in such a manner that when that particular name with the special character needs to be retrieved it's sorted with the names at the end of the alfabet. So it looks like this:
-------------
LAST_NAME
-------------
andersen
boudreau
zimmermann
ecklund
ismael

Could you give me a solution ?

thanks,

dax

ADD. Info:
Perhaps worth to know....changing character set means building the DB from ground up again...that's not an option, besides, I don't JUST have names from turkey, but potentially from all over the world.

I consulted a fellow-developer of mine and he said that it's not possible to do this thru character sets or such. I have to make a new column and a procedure that sorts the names in the new/preliminary column and converts it to the right column.

ibro

If you configure your database to use Turkish character order set the sorting will be like this:
A B C C (with tail) D E F G G (with hat) H
I (with dot) I (without dot) K L M N O O (with dots) P Q R
S S (with tail) T U U (with dots) V W X Y Z

Unfortunately I can't tell what will happend to some other characters (like French or Spain specific characters).
I assume your database uses Unicode, isn't it?

ahoor

Can your database be changed to a different characterset? I mean because of the applications using it... what will the effect of a change be?

You can of course make (1 time work only) create a table with the correct order of the characters...
like

create table volgorde
( id numeric(4) identity
, letter char(1) not null
)
go
insert into volgorde (letter) values ('a')
insert into volgorde (letter) values ('a')
insert into volgorde (letter) values ('a')
etcetera...

And use this table in the join

from your_table, volgorde
where volgorde.letter = substring(last_name,1,1)
order by volgorde.id

Of course you have to adjust the substring and other statements to Oracle specific...
Hope this helps,

Arjan

ahoor

Of course I meant a-umlaut and a with a dakje and char's like that, but it seems EE doesn't support them??

kouke

Maybe you can use nls_sort....

SQL> select * from test order by name;

NAME
--------------------------------------------------
??
ao
ao
ao
Aaaa fsdfsd
Aaaa fsdfsd
Oooo
Oooo

SQL> alter session set nls_sort=binary
2 ;

Istunto on muutettu.

SQL> select * from test order by name;

NAME
--------------------------------------------------
??
Aaaa fsdfsd
Oooo
ao
ao
ao
Aaaa fsdfsd
Oooo

SQL> select * from test order by nlssort('name,nlssort=finnish');

NAME
--------------------------------------------------
Aaaa fsdfsd
Aaaa fsdfsd
Oooo
Oooo
??
ao
ao
ao

greetings from finland...

stmontgo

following this thread -)

ser6398

Here is a potential work around:

Create a table that looks like this:

alphabet order_nbr
a 1
b 2
c 3
d 4
...

Add in your special characters and order them appropriately (and you can add more characters as needed). Anytime you want to correct the order, join your query to this table (using substr(name,1,1)), and order by ORDER_NBR column. It's not the nicest looking solution, but I think it will work well.

daximus

ASKER

I was wondering...how come there isn't a formal solution to this problem.

Doesn't Oracle comply to some sort of standard for this? Unicode, ASCII, whatever?

I mean I hear quite a lot of stories with problems like this.

Is there only a work-a-round?

thanks.....

ahoor

Ser6398 that's what I said too....

ser6398

Sorry ahoor, I didn't see your suggestion.

UsamaMunir

The session which retrieves the data, set it's nls_language parameter like this

alter session set nls_language = 'finnish'

Now if u retrive the results sorting by name, it sorts it by finnish language.

I hope it helps

Regards
UsamaMunir

nico5038

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)

ASKER CERTIFIED SOLUTION

Jgould

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial