Possible to ignore case in ACCESS statement?

[Robert Edis]

SQL Server will do a table scan if a function is used
in the select logic where clause.

Blue

--- Peter Bateman <pfbcs@hotmail.com> wrote:

> Hi all:
> 
>   I believe for most databases the code:-
> 
>        select * from client_view &
>          where upper(client_name) =
> :upper(t_client_name)
> 
> will result in a table scan as opposed to an index
> scan  you may want
> to get rid of your index or change your index
> perhaps to a soundex column or
> an upshifted client_name column.
> 
> Some databases allow you to index on a calculated
> column.
> 
> Regards,
> Peter Bateman
> 
> 
> 
> >I was thinking along the lines of your 3rd
> suggestion ("Use a view that
> >upper cases the client name..."), but could find no
> SQL function to shift
> >the field to uppercase. My manuals were printed Dec
> 1991. Tried UPPER() and
> >it works great!
> >Thanks for the tip.
> >Martyn
> >
> >-----Original Message-----
> >From: Darren Reely
> [mailto:darren.reely@latticesemi.com]
> >Sent: Friday, April 29, 2005 4:28 PM
> >To: Thomson, Martyn EDUC:EX
> >Cc: POWERHOUSE NEW (E-mail)
> >Subject: Re: Possible to ignore case in ACCESS
> statement?
> >
> >
> >I can think of three ways to do this.
> >
> >You could customize the POSTPATH procedure to
> adjust the client_name to
> >begin with an upper case letter. The problem with
> this is, what if the
> >user types "acme c@" but the database entry is
> "Acme Company".
> >
> >Use a cursor to upper or lower case both sides of
> the equation. This
> >isn't optimal but is usable. I think you'd need to
> use a temporary value
> >for the prompt field.
> >
> >Here is some untested/unverified code:
> >
> >    temporary t_client_name varchar*30 reset at
> startup
> >    sql in PR declare myview cursor for &
> >      select * from client_view &
> >      where upper(client_name) =
> :upper(t_client_name)
> >
> >Use a view that upper cases the client name and
> then force the same for
> >the entry on the view screen. Then just redisplay
> what you get back.
> >Simple to maintain and you don't have to worry
> about cursor quirks of PH.
> >
> >Good luck.
> >
> >Darren
> >
> >P.S. I don't suggest you do "select *" unless you
> really want everything
> >in a record. Only select what you'll be using,
> otherwise you'll take a
> >performance hit.
> >
> >
> >Thomson, Martyn EDUC:EX wrote:
> > >>Hi,
> > >>I have a simple inquiry screen that retrieves a
> view of client data by
> > >>client Id or name. Most but all of the names are
> stored in title case.
> > >>
> > >>file Client_view IN PR PRIMARY transaction query
> occurs 1 cache ˇ
> > >>access via client_id request client_id
> > >>access via client_name request client_name
> orderby client_name
> > >>
> > >>Retrieval on client_name is generic so trailing
> wildcard "@" can be 
> >used,
> > >>but is there any way to ignore case? Currently
> the user has to remember 
> >to
> > >>capitalize the first letter.
> > >>
> > >>Martyn
> > >>OpenVMS Alpha V7.3-1, OpenVMS Alpha V7.3-1;
> Powerhouse 7.10G1
> > >>
> > >
> > >
> >
> >--
> >
> >-----------------------------------
> >HELP STOP SPAM. STOP USING WINDOWS.
> >-----------------------------------
> >
> >--
> >= = = = = = = = = = = = = = = = = = = = = = = = = =
> = =
> >Mailing list: powerh-l@lists.sowder.com
> >Subscribe: "subscribe" in message body to
> powerh-l-request@lists.sowder.com
> >Unsubscribe: "unsubscribe &lt;password&gt;" in
> message body to 
> >powerh-l-request@lists.sowder.com
> >http://lists.sowder.com/mailman/listinfo/powerh-l
> >This list is closed, thus to post to the list you
> must be a subscriber.
> 
>
_________________________________________________________________
> MSNŽ Calendar keeps you organized and takes the
> effort out of scheduling 
> get-togethers. 
>
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
> 
>   Start enjoying all the benefits of MSNŽ Premium
> right now and get the 
> first two months FREE*.
> 
> -- 
> = = = = = = = = = = = = = = = = = = = = = = = = = =
> = =
> Mailing list: powerh-l@lists.sowder.com
> Subscribe: "subscribe" in message body to
> powerh-l-request@lists.sowder.com
> Unsubscribe: "unsubscribe &lt;password&gt;" in
> message body to powerh-l-request@lists.sowder.com
> http://lists.sowder.com/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you
> must be a subscriber.
>