Case-Insensitive Sorting and Record Selection with Query/400
June 22, 2005 Hey, Ted
Thanks for explaining how to ignore case when sorting and selecting records with SQL. Can you tell me how to do the same with Query/400?
Sure. First, here’s a database file that we can query.
LASTNAME FIRSTNAME smith billy Smith Amos SMITH CHARLEY smith DICK Smith ELMO SMITH dan JONES Andy
The normal sort places lowercase letters before uppercase ones, like this:
LASTNAME FIRSTNAME smith billy smith DICK JONES Andy Smith Amos Smith ELMO SMITH dan SMITH CHARLEY
From the Define the Query panel, place a 1 (one) beside the Select collating sequence option and press Enter. If your system has a collating sequence option of 2 (Query for iSeries English), you can use this option. If not, select option 5 (System sort sequence). On the following panel, Select System Sort Sequence, select Sort sequence 3 (Shared) and a Language id of *JOBRUN. Our example data sorts alphabetically.
LASTNAME FIRSTNAME JONES Andy Smith Amos smith billy SMITH CHARLEY SMITH dan smith DICK Smith ELMO
You will also be able to select data regardless of case. Here’s an example from the Select Records panel.
Field Test Value FIRSTNAME LIKE 'D%'
All records in the resulting dataset have first names that begin with either capital or lowercase D.
LASTNAME FIRSTNAME SMITH dan smith DICK
The system would have given me the same results if I had coded a lowercase d in the Value expression.
Field Test Value FIRSTNAME LIKE 'd%'
Case-Insensitive Sorting and Record Selection in SQL/400