Re: Manipulation of strings query

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