Re: 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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.