Re: Mixed case search in sql

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Just saw that expressions in like patterns and escape characters is new
in v5r1. upper() on the right of like may not work in earlier releases.

Adam Lang wrote:
>
> Usually what you can do is just surround both values with the UPPER()
> function.  It makes all values uppercase and so as not to have to worry
> about the case sensitivity.
>
> Adam Lang
> Systems Engineer
> Rutgers Casualty Insurance Company
> http://www.rutgersinsurance.com
> ----- Original Message -----
> From: "Valerio Vincenti" <VVincenti@spotsylvania.va.us>
> To: <midrange-l@midrange.com>
> Sent: Friday, February 22, 2002 11:52 AM
> Subject: Mixed case search in sql
>
> > Hi Group,
> >
> > I apologize in advance if I'm posting on the wrong list or if this issue
> has already come up in the past (I couldn't find any reference in the
> archives).
> >
> > My goal is to code a sql statement to retrieve records based  on a
> wildcard search on the street name. The sql will be embedded in an inquiry
> program which is meant to reload the subfile according to the user
> selection.
> >
> > In our shop, Street Addresses are stored in mixed case (ie: Marye Rd).
> >
> > A statement like: select street from lib/file where street like '%marye%',
> finds 'marye Rd' but not 'Marye Rd'.
> >
> > In query I can resolve this problem by specifying option# 3 on the
> collating sequence to make the search not case sensitive.
> >
> > Is there a similar method to be used in sql? I tried to compare the qmqry
> sources of a query that uses the standard collating sequence and of another
> one that uses the type 3 collating sequence, but they look identical.
> >
> >
> > Thanks for any help on this matter.
> >
> > Valerio Vincenti
> > Systems Analyst
> > County of Spotsylvania, I.S. Department
> > Spotsylvania, VA 22553
> > Phone (540) 582-7055 Ext. 233
> > Fax (540) 582-6304
> > e-mail: vvincenti@spotsylvania.va.us
> >
> >
> > _______________________________________________
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > or email: MIDRANGE-L-request@midrange.com
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.

--
Vern Hamberg

Would you like to see a challenging little arithmetic puzzle
that might get you or your kids or grandkids more interested
in math? Go to <http://cgi.wff-n-proof.com/MSQ-Ind/I-1E.htm>

Sillygism--

Something is better than nothing.
Nothing is better than a ham sandwich.
Ergo
Something is better than a ham sandwich.