How to set the order of characters in TDBGrid sorting?

Question

I'm using a TDBGrid connected to a TDataSource. This TDataSource uses a TADOQuery as its dataset. The TADOQuery connects to a Oracle10g database and holds the following query:

SELECT ST.desc
FROM my.subsection ST
WHERE  ST.date_disp  = :dated
ORDER BY ST.desc

ST.desc is a string, and generally contains data like these:

'1st place'
'2nd place'
'A zone'
'Her zone'
'My zone'
'Zone'

Everything works perfectly, except that when I run the query in SQLTools, it returns the data sorted by LETTERS first, then NUMBERS. e.g.:

'A zone'
'Her zone'
'My zone'
'Zone'
'1st place'
'2nd place'

But when I run the application, the result is NUMBERS first, and then LETTERS! e.g:

'1st place'
'2nd place'
'A zone'
'Her zone'
'My zone'
'Zone'

I figured out that Oracle uses one sorting linguistic (http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm) and some Delphi component (TDBGrid or TDataSource?) another.

I then tried to make the result of the query fit Delphi's component order, which is NUMBERS then LETTERS. But this is not desired since the natural order for the users is LETTERS, then NUMBERS.

Does anyone know how I can be sure of which component is "re-sorting" the data and how can I make both sort orders the same?

This sounds like Oracle has NLS_SORT or at least NLS_LANGUAGE set to a different codification than the language codification of the computer where you are doing the tests. I would start by unlink the Tdatasource of the query component and check in which order the elements are returned. — Guillem Vicens
What do you mean with: "I would start by unlink the Tdatasource of the query component"? I've already checked that they both return different orders... The whole question is about how can I check/change the linguistic used by the component in Delphi. — gustavogbc
the TDbGrid does no sorting on its own. It is just used to show the data. The sorting (if done) is usually done by the query component instead. — Guillem Vicens
I'm not convinced of that, @GuillemVicens. Please read below for further details. — gustavogbc
I must say I agree with what @GolezTrol says in his last comments. The sorting is almost surely done by the ADO components. Not sure if you did, but you could try checking you have the latest Oracle ADO driver as well as the latest ADO version. Hope you find the solution :-) — Guillem Vicens

GolezTrol GolezTrol · Accepted Answer · 2013-06-02T09:06:57

You can specify what kind of sorting/comparison is used by using the NLSSORT function.

SELECT ST.desc
FROM my.subsection ST
WHERE  ST.date_disp  = :dated
ORDER BY 
  NLSSORT(ST.desc, 'NLS_SORT=BINARY_AI')

Instead of specifying the second parameter, you can also call nlssort with just the value, and set the default sorting in the session (after you connect to the database). The advantage of that, is that you can easily make a global change to it later, or even let the user choose their favourite way of sorting:

ALTER SESSION SET NLS_SORT = 'BINARY_AI';

In reverse, you can check the current sorting:

select value from v$nls_parameters
WHERE parameter = 'NLS_SORT'

How to set the order of characters in TDBGrid sorting?

1 Answers