Can SetOrderBy sort case insensitive?

[softsmith]

Delphi XE2 with SP 4
IBDAC version 4.1.5

When SetOrderBy(fieldName) is used, can the resultant sort be case insensitive ?
At the moment, all the upper case items are displayed first, followed by the lower case items, when I really want it to be in strictly alphabetical order, regardless of case

I have set the TIBConnection option DefaultSortType to stCaseInsensitive, but this does not seem to affect the SetOrderBy function.

Thanks for your help.

Colin

[softsmith]

I'm sorry, but I should have also added, can the sort also be in reverse order ?

[AndreyZ]

Hello,

The SetOrderBy method adds to the initial query the ORDER BY statement. Case sensitivity is set by the collation in a database. To obtain case-insensitive results using the ORDER BY statement, you should use the character set for columns in your database for which the case-insensitive collations are defined. For example, for the following table:

Code: Select all

CREATE TABLE TEST (
  ID   INTEGER PRIMARY KEY,
  TXT  VARCHAR(10) CHARACTER SET UTF8
);

you can use the following code:

Code: Select all

IBCQuery.SQL.Text := 'SELECT * FROM TEST';
IBCQuery.SetOrderBy('TXT COLLATE UNICODE_CI');
IBCQuery.Open;

You can find more information about the case-insensitive (CI) collations here: http://www.firebirdsql.org/refdocs/lang ... tions.html
To make reverse sorting, you should use the DESC statement in the following way:

Code: Select all

IBCQuery.SQL.Text := 'SELECT * FROM TEST';
IBCQuery.SetOrderBy('TXT COLLATE UNICODE_CI DESC');
IBCQuery.Open;

You can find more information about the ORDER BY statement here: http://www.firebirdsql.org/refdocs/lang ... 15-orderby