Re: Mixed case search in sql

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Try the upper() function:

select street from lib/file where upper(street) like '%MARYE%'

Valerio Vincenti wrote:
>
> 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.

--
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.