Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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.

 
0
daximus
Asked:
daximus
1 Solution
 
ibroCommented:
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
 
ahoorCommented:
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
 
ahoorCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
koukeCommented:
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
 
stmontgoCommented:
following this thread -)
0
 
ser6398Commented:
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
 
daximusAuthor Commented:
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
 
ahoorCommented:
Ser6398 that's what I said too....
0
 
ser6398Commented:
Sorry ahoor, I didn't see your suggestion.  
0
 
UsamaMunirCommented:
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
 
nico5038Commented:

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
 
JgouldCommented:
Question has been closed as per recommendation

JGould-EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now