Case insensitive searching with SQL

This is mostly for the archives as I couldn’t quickly and easily find a prior example.

Consider a CUSTOMERS table containing a mixed case CUSTNAME column.

To search it, many will use the UPPER() or LOWER() functions to mono case the field like so:

Select * from customers
Where upper(custname) = upper(CustomerNameToFind)


However, this results in poor performance since an index can’t be used.

A better solution is to create a case insensitive index and perform your search in a case insensitive
manner.

To create the case insensitive index, all you need to do is create the index from a STRSQL or “Run SQL
Scripts” session that is currently set to ignore case.
For STRSQL:
1) F13 – Services
2) Option 1 – Changes Session attributes
3) Change “Sort Sequence” on the 2nd page to *LANGIDSHR

For iNav’s Run SQL Scripts
1) Connection→JDBC Setup
2) Language Tab
3) Change “Sort Based On” to Language ID
4) Make sure “Shared” radio button under “Sort Weight” is selected



Now to make use of the index….
For interactive SQL , follow the same steps as above to switch your session’s sort sequence.

For a stored procedure, UDF, or embedded SQL, add the statement
SET OPTION SRTSEQ = *LANGIDSHR

For JDBC connections, add the follow to the connection string:
sort=language;sort weight=shared

ex:
jdbc:as400://MYAS400;date format=iso;sort=language;sort weight=shared




Charles Wilt 
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307
wiltc@xxxxxxxxxx



This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.