Solved

Sorting different sorts of characters in 8.0.5. ???

Posted on 2001-07-13
12
354 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

710 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