oracle underscore sort problem

If "title" field is varchar2 type the sorting will not ingnore underscore character. Could you tell me exactly the type of "title" field?

title field is of type varchar2, size 255

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

title field is of type varchar2, size 255

In my case underscore is just not considered does not matter if the characters are lowercase or uppercase.

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

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.