c# string sorting VS Oracle string sorting

Question

Hi!

I have to sort a list of strings in exactly the same way as they are returned to me from an oracle database. Unfortunately Oracle returns them in such a way, that numeric characters are sorted after alphabetic. For example:

Alabama
x-men
100 new ideas
9 months
...

How could I sort a list of strings in C# in such a way?

Answer 1

You can implement a custom class derived from StringComparer to exactly replicate the results.

However, Oracle's string sorting likely is determined by the collation used in the database/table/query so when using the same culture for sorting you may get the same results already without resorting to writing too much on your own.

Answer 2

You could create your own IComparer class, and use that instance to sort your list of strings.

List<T> has a member method 'Sort' which takes an IComparer or Comparision, in which you can implement your order-logic.

Answer 3

Hi Jure,

Oracle can sort in several ways. By default Oracle will sort strings (VARCHAR) in a binary way, ie: strings will be sorted by the numeric values of each character in the database character set.

SQL> select * from nls_session_parameters where parameter like '%SORT%';

PARAMETER  VALUE
---------- ----------
NLS_SORT   BINARY

SQL> SELECT rownum, chr(ROWNUM) FROM dual CONNECT BY LEVEL <= 127 ORDER BY 2;

    ROWNUM CHR(ROWNUM)
---------- -----------
[...]
        48 0
        49 1
        50 2
        51 3
[...]
        65 A
        66 B
        67 C
        68 D
[...]
        97 a
        98 b
        99 c

You can alter the sorting behaviour:

SQL> alter session set nls_sort=french;

Session altered

SQL> SELECT rownum, chr(ROWNUM) FROM dual CONNECT BY LEVEL <= 127 ORDER BY 2;

    ROWNUM CHR(ROWNUM)
---------- -----------
[...]
        65 A
        97 a
        66 B
        98 b
[...]
        90 Z
       122 z
        48 0
        49 1
        50 2
[...]