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?

–Ron

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%'

–Ted

RELATED STORY

Case-Insensitive Sorting and Record Selection in SQL/400