Solved

Sorting different sorts of characters in 8.0.5. ???

Posted on 2001-07-13
12
314 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
12 Comments
 
LVL 3

Expert Comment

by:ibro
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Expert Comment

by:kouke
Comment Utility
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
Comment Utility
following this thread -)
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:daximus
Comment Utility
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
Comment Utility
Ser6398 that's what I said too....
0
 
LVL 5

Expert Comment

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

Expert Comment

by:UsamaMunir
Comment Utility
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
Comment Utility

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
Comment Utility
Question has been closed as per recommendation

JGould-EE Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now