Solved

oracle underscore sort problem

Posted on 2001-09-11
8
2,898 Views
Last Modified: 2007-12-19
I have a problem when I execute an SQL query on oracle:
Ex:
select uid, title from table_name order by title

The title field is of type string but the sorting ignores the underscore character'_' when is sorting records. Ex:

aaa
bbb
__cccc
cddd

I want that underscore to be considered when sorting. How can I sove this problem?
0
Comment
Question by:lehene
[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
8 Comments
 
LVL 1

Expert Comment

by:mona1974
ID: 6473908
If "title" field is varchar2 type the sorting will not ingnore underscore character. Could you tell me exactly the type of "title" field?
0
 

Author Comment

by:lehene
ID: 6473925
title field is of type varchar2, size 255
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6473931
The underscores ARE taken into account when sorting using ORACLE default sort.

The sequence of sorting is similar to that of the ASCII table.  In the case of underscores, it represents character 95 in the ascii table.

Thus the underscores will come AFTER uppercase characters, but BEFORE lowercase characters.

I tested with the following results:

AAA
BBB
CCC
DDD
_CC
__C
__C
__c
_cc
aaa
bbb
ccc
ddd

IF you have a particular placement in mind, then you can try using a DECODE function or a REPLACE function in your ORDER BY clause.

JT
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:lehene
ID: 6473933
title field is of type varchar2, size 255
0
 

Author Comment

by:lehene
ID: 6473961
In my case underscore is just not considered does not matter if the characters are lowercase or uppercase.
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 6473972
I'm afraid I can't believe that.  Oracle does not ignore underscores.  Do you only use upper or lower case?

Can you show us the results from a sort that has both upper and lower case where the results are sorted by the the field that starts with the underscore and demonstrate that it has been ignored? (i.e. please show the cursor/select code and the output results)

JT
0
 

Expert Comment

by:Kirilloff
ID: 6474172
There is NLS_SORT parameter that affect sort order. If you use ASCII7 or BINARY then underscores are taken into account. If you use other sortings then underscores are insignificant. Try to use

ALTER SESSION SET NLS_SORT=ASCII7

But in this case you will sort strings by their ascii codes: at first some signs, then digits, capital letters, underscore and some signs, then small letter.
0
 
LVL 3

Accepted Solution

by:
Wadhwa earned 150 total points
ID: 6474967
Hi Lethene,

The above comments are good in all respect. I want you to check the following.
connect with sys and execute the following query

ora816 SamSQL :> select * from nls_database_parameters
  2  where parameter='NLS_SORT';


it will tell you what the value of sort (ASCII/BINARY/WEST_EUROPEAN etc )

If your database is on NT check the registry entry on
HKLM/SOFTWARE/ORACLE or for multiple home HKLM/SOFTWARE/ORACLE/HOMEn

NLS_SORT on the client machine also effect the results of sorting.


Here are the simple test for ascii7/binary/west_european sort. Your above result matches with the west_european sort

ora816 SamSQL :> alter session set nls_sort=ascii7
  2  /

Session altered.

Elapsed: 00:00:00.00
ora816 SamSQL :> select x,ascii(x) from testsort order by 1;

X  ASCII(X)
----------
__cc        95
_ccc        95
aaa        97
bbbb        98
ccc        99
dddd       100

6 rows selected.

Elapsed: 00:00:00.40
ora816 SamSQL :> alter session set nls_sort=BINARY
  2  /

Session altered.

Elapsed: 00:00:00.00
ora816 SamSQL :> select x,ascii(x) from testsort order by 1;

X  ASCII(X)
----------
__cc        95
_ccc        95
aaa        97
bbbb        98
ccc        99
dddd       100

6 rows selected.

Elapsed: 00:00:00.40
ora816 SamSQL :> alter session set nls_sort=west_european;

Session altered.

Elapsed: 00:00:00.00
ora816 SamSQL :> select x,ascii(x) from testsort order by 1;

X    ASCII(X)
---
aaa        97
bbbb       98
__cc       95
_ccc       95
ccc        99
dddd       100


6 rows selected.

Elapsed: 00:00:00.50
ora816 SamSQL :>

Hope the above will explain why you are not seeing the desired sorting results.

Goodluck
Sam
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

728 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