Loading ...
Sorry, an error occurred while loading the content.

Re: searching for case mismatched item

Expand Messages
  • Mat
    When you said can it be done at the database level, you are most likely referring to the use of a computed column... e.g. ALTER TABLE part# ADD ucasepart AS
    Message 1 of 17 , Jun 1, 2010
    • 0 Attachment
      When you said can it be done at the database level, you are most likely referring to the use of a computed column...

      e.g. ALTER TABLE part# ADD ucasepart AS UCASE(part#)

      If you don't get anywhere with the ACS file, this is probably the best MS-SQL server based solution to look at - use a computed column that is defined as UCASE(part#) and then use that for your locating.

      This is added as a new column to the existing table and SQL computes it each time you retrieve a value. If performance is an issue then you would have the option of creating a new index using the computed column to make it run as quickly as possible.

      Best Regards,
      Mat Wood

      Safe Computing Ltd
      Leicester
      UK

      --- In magicu-l@yahoogroups.com, sherman levine <slevine@...> wrote:
      >
      > Alan,
      > Thanks for the suggestion. I have a case-insensitive mssql acs file from
      > Steve which should do the trick.
      > Sherm
      >
      > ----- Original Message -----
      > From: "Alan Brookes" <alanjbrookes@...>
      > To: <magicu-l@yahoogroups.com>
      > Sent: Sunday, May 30, 2010 12:57 PM
      > Subject: Re: [magicu-l] Re: searching for case mismatched item
      >
      >
      > > Hi Sherm,
      > > another possibility is to create a View where instead of returning Part#
      > you return UCASE(Part#).
      > >
      > > Make this column a Virtual Index: I have a hunch you'll be pleasantly
      > surprised at the performance speed.
      > >
      > >
      > >
      > >
      > > Best Regards
      > >
      > > Alan
      > >
      > > On 30-05-2010 17:38, sherman levine wrote:
      > > > It's his problem, let it be his solution :-)
      > > >
      > > > Sherm
      > > >
      > > > ----- Original Message -----
      > > > From: "ola_ip"<omar.lamin@...>
      > > > To:<magicu-l@yahoogroups.com>
      > > > Sent: Sunday, May 30, 2010 11:36 AM
      > > > Subject: [magicu-l] Re: searching for case mismatched item
      > > >
      > > >
      > > >> It most likely can. Ask the DBA if he can set a trigger to
      > automatically
      > > > update UpperofPrinaryKey upon any update of PrimaryKey.
      > > >>
      > > >> Omar,
      > > >>
      > > >> --- In magicu-l@yahoogroups.com, sherman levine<slevine@> wrote:
      > > >>>
      > > >>> Omar,
      > > >>> Can this not be done at the database level with MSSQL 2005?
      > > >>> Sherm
      > > >>>
      > > >>> ----- Original Message -----
      > > >>> From: "ola_ip"<omar.lamin@>
      > > >>> To:<magicu-l@yahoogroups.com>
      > > >>> Sent: Sunday, May 30, 2010 8:37 AM
      > > >>> Subject: [magicu-l] Re: searching for case mismatched item
      > > >>>
      > > >>>
      > > >>>> BTW, even in this separate field approach, all programs of the
      > > > external
      > > >>> system that maintain PrimaryKey also need to maintain the new
      > > >>> UpperofPrinaryKey or things will deteriorate with time.
      > > >>>>
      > > >>>> Omar,
      > > >>>>
      > > >>>> --- In magicu-l@yahoogroups.com, sherman levine<slevine@> wrote:
      > > >>>>>
      > > >>>>> Not my data - and it's part of a much larger external system
      > > >>>>> Best I could ask for is an additional field set to upper(keyfield)
      > > >>>>> Sherm
      > > >>>>>
      > > >>>>> ----- Original Message -----
      > > >>>>> From: "ola_ip"<omar.lamin@>
      > > >>>>> To:<magicu-l@yahoogroups.com>
      > > >>>>> Sent: Saturday, May 29, 2010 5:38 PM
      > > >>>>> Subject: [magicu-l] Re: searching for case mismatched item
      > > >>>>>
      > > >>>>>
      > > >>>>>> If ABC is meant to be really the same as aBC, why not run a batch
      > > > task
      > > >>> to
      > > >>>>> update Keyfield with Upper(Keyfield) and ,Modify every program
      > > >>> (typically
      > > >>>>> few) that accepts a Keyfield value to accept upper case only?
      > > >>>>>>
      > > >>>>>> Omar,
      > > >>>>>>
      > > >>>>>> --- In magicu-l@yahoogroups.com, sherman levine<slevine@> wrote:
      > > >>>>>>>
      > > >>>>>>> Keith,
      > > >>>>>>> Are you saying that if the user enters
      > > >>>>>>>
      > > >>>>>>> vEntry=ABC
      > > >>>>>>> and the record value is aBc
      > > >>>>>>>
      > > >>>>>>> I'll get a valid link from one to the other?
      > > >>>>>>>
      > > >>>>>>> My (albeit brief) experience where a case-mismatched writelink
      > > >>> returned
      > > >>>>> a
      > > >>>>>>> duplicate key error suggests that's not the case.
      > > >>>>>>>
      > > >>>>>>>
      > > >>>>>>>
      > > >>>>>>>
      > > >>>>>>> ----- Original Message -----
      > > >>>>>>> From: "Keith Canniff"<kcanniff@>
      > > >>>>>>> To:<magicu-l@yahoogroups.com>
      > > >>>>>>> Sent: Saturday, May 29, 2010 5:09 PM
      > > >>>>>>> Subject: RE: [magicu-l] searching for case mismatched item
      > > >>>>>>>
      > > >>>>>>>
      > > >>>>>>>> I'm confused because if MSSQL is set to case insensitive, then
      > > > it
      > > >>>>>>> shouldn't
      > > >>>>>>>> matter if they key it in upper, lower, or mixed case to find
      > > > the
      > > >>>>> record,
      > > >>>>>>>> without a virtual link.
      > > >>>>>>>> Or are you only wanting matches if they EXACTLY match what the
      > > >>> user
      > > >>>>> has
      > > >>>>>>>> entered (case sensitive)?
      > > >>>>>>>>
      > > >>>>>>>> -----Original Message-----
      > > >>>>>>>> From: magicu-l@yahoogroups.com
      > > > [mailto:magicu-l@yahoogroups.com]
      > > >>> On
      > > >>>>> Behalf
      > > >>>>>>>> Of sherman levine
      > > >>>>>>>> Sent: Saturday, May 29, 2010 4:11 PM
      > > >>>>>>>> To: magic maillist
      > > >>>>>>>> Subject: [magicu-l] searching for case mismatched item
      > > >>>>>>>>
      > > >>>>>>>> I have a client whose part# table consists of approx 500k
      > > >>> mixed-case
      > > >>>>>>>> records. MSSQL 2005.
      > > >>>>>>>> Part# is the primary key. Case insensitive collation.
      > > >>>>>>>>
      > > >>>>>>>> The user will type in a part# in upper case and the system
      > > > should
      > > >>>>> display
      > > >>>>>>>> the linked part - ignoring the case mismatch.
      > > >>>>>>>>
      > > >>>>>>>> I know I can make this work with a structure such as
      > > >>>>>>>>
      > > >>>>>>>> vEntry
      > > >>>>>>>> BeginLink to Part# table
      > > >>>>>>>> Part#
      > > >>>>>>>> vLinkOk Init to [Upper(vEntry)=Upper(Part#)]. Locate to
      > > >>> true/true
      > > >>>>>>>> EndLink
      > > >>>>>>>>
      > > >>>>>>>> but anticipate that the retrieval would be slow.
      > > >>>>>>>>
      > > >>>>>>>> Should I ask the DBA to add a field "PART_UPPER" containing
      > > > the
      > > >>> part#
      > > >>>>> in
      > > >>>>>>>> upper case and change vEntry to upper case?
      > > >>>>>>>>
      > > >>>>>>>> Any other method?
      > > >>>>>>>>
      > > >>>>>>>> Thanks
      > > >>>>>>>>
      > > >>>>>>>> Sherm
      > >
      > >
      > > ------------------------------------
      > >
      > > Yahoo! Groups Links
      > >
      > >
      > >
      > >
      > >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.