Re: Manipulation of strings query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 08:40:18 -0500

StressedMonkey wrote:
> Hi Bob,
>
> The DB is Oracle 8i I think.
>
> I have received the data in an excel spreadsheet which contains the
> names in the LASTNAME, FIRSTNAME format and was going to just import
> it into a table in the DB. I don't know any way of getting excel to
> split a cell into 2 so that I could enter it as 2 fields in a
> database and I'm certainly not doint it manually as there are about
> 2000 rows!
>
Sorry, but there is no foolproof method. You will not be able to do this
without some manual intervention to make sure that whatever method you used
handled all the names correctly.

> The problem with the string input is that users will not be entering
> the data on a form as such, the application performs an LDAP query
> agains a last name that they enter. The query then returns the names
> in the Firstname (Space) Lastname format............

So modify the query so it returns the names in separate columns ... or is
that out of your control as well?

Here is a simple vbscript way to split a string containing two words
separated by a single space into two strings, each containing a word:

dim str,arSplit,sFirst, sLast, sFormatted

str="FirstName LastName"
arSplit=Split(str," ")
sFirst = arSplit(0)
sLast = arSplit(1)

To combine these and separate them by a comma:

sFormatted = sLast & ", " & sFirst

This will work for most names. The problem arises with names like:

Nicholas Von Helsing
George Hamilton III
Morton Downey, Jr.
Anita Martinez Gonzales
Pamela Sue Anderson

Somebody is going to have to scrutinize the results of any algorithm you
decide to use and verify that the unusual names were handled correctly.

Bob Barrows 

-- 
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Relevant Pages

  • Re: Manipulation of strings query
    ... >> I have received the data in an excel spreadsheet which contains the ... The query then returns the names ... >So modify the query so it returns the names in separate columns ... ... >Bob Barrows ...
    (microsoft.public.inetserver.asp.general)
  • Re: Refining query
    ... Have you ever heard the joke about Bob ... so...if I were to create a DISTINCT query of "qryBigSet" on fields ...
    (microsoft.public.access.queries)
  • Re: Excel 08 - Splitting tables
    ... Couldn't that be accomplished using the Advanced Filter with a separate ... criteria range on each target sheet & using the "Type" as the criteria? ... Bob Jones ... Ref Type Date Location ...
    (microsoft.public.mac.office.excel)
  • Re: All hail Bob (not)!
    ... casual user to get a correct result for a simple query, you only confirm that you are an embecile. ... Much more curiously though your resort to bombastic posts (whilst a feeble trait IMO for anyone professing intellectual tendencies) seems to have recent CDTzens in thrall given the deafening silence to date. ... What is it with Bob though? ...
    (comp.databases.theory)
  • RE: Summing Fields
    ... query for the order details that match the order shown on the main form. ... shown on the subform. ... "Bob Love" wrote: ... I use a query to find all details that match the order # in the main form. ...
    (microsoft.public.access.gettingstarted)