Re: Mixed case search in sql

[Martin Rowe <martin@xxxxxxxxxx>]

On Fri, 2002-02-22 at 16:52, 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

Hi Valerio

On STRQMQRY you can specify SRTSEQ(*LANGIDSHR)[1] and there is the same
option behing F13 in interactive SQL (STRSQL) - don't know about
embedded SQL (if that's what you're using). The actual syntax of the SQL
is identical AFAIK.

Regards, Martin
[1] It's a handy parameter to have, so I recently added it to my EXCSQL
QMQRY wrapper. http://www.dbg400.net/excsql.html
--
martin@dbg400.net  jamaro@firstlinux.net  http://www.dbg400.net
/"\
DBG/400 - DataBase Generation utilities - AS/400 / iSeries Open        \
/
Source free test environment tools and others (file/spool/misc)
X
[this space for hire]  ASCII Ribbon Campaign against HTML mail & news  /
\