Solved

Sorting different sorts of characters in 8.0.5. ???

Posted on 2001-07-13
12
360 Views
Last Modified: 2012-08-13
"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.

 
0
Comment
Question by:daximus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 3

Expert Comment

by:ibro
ID: 6280644
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?
0
 
LVL 3

Expert Comment

by:ahoor
ID: 6280785
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


0
 
LVL 3

Expert Comment

by:ahoor
ID: 6280790
Of course I meant a-umlaut and a with a dakje and char's like that, but it seems EE doesn't support them??
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 1

Expert Comment

by:kouke
ID: 6284227
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...


0
 
LVL 2

Expert Comment

by:stmontgo
ID: 6284579
following this thread -)
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6286029
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.
0
 

Author Comment

by:daximus
ID: 6286081
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.....
0
 
LVL 3

Expert Comment

by:ahoor
ID: 6286082
Ser6398 that's what I said too....
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6286155
Sorry ahoor, I didn't see your suggestion.  
0
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6301448
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7259003

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

Accepted Solution

by:
Jgould earned 0 total points
ID: 7289115
Question has been closed as per recommendation

JGould-EE Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question