Manipulation of strings query

[StressedMonkey]

Hi,

I'm not too hot on my manipulation of strings. I have a database which
cintains names in the format (Upper case also) LASTNAME, FIRSTNAME

I want to search against that database, but the data on the form to produce
the string will be input in the following format:

Firstname Lastname

So I will need to convert this string in the following ways:

1. Make all uppercase
2. Reverse the position of Firstname & Lastname
3. Add a comma and space between Lastname & Firstname

Basically, so that the string looks exactly as the names will in the
database.

Are there any Maestro's out there that can help me with some code to achieve
this?

Thanks.

Miguel.

[Bob Barrows]

StressedMonkey wrote:

Hi,

I'm not too hot on my manipulation of strings. I have a database which
What database?? This is always relevant.
cintains names in the format (Upper case also) LASTNAME, FIRSTNAME
Bad idea. These should be in two separate fields.

I want to search against that database,
but the data on the form to
produce the string will be input in the following format:

Firstname Lastname
Bad idea. Users should be instructed to enter the data in the format in
which it appears in the database. Alternatively, you should have two
separate textboxes, one for the first name, and one for the last

So I will need to convert this string in the following ways:

1. Make all uppercase
With most databases, searches are case insensitive, so this step is usually
not necessary.

2. Reverse the position of Firstname & Lastname
3. Add a comma and space between Lastname & Firstname

Now we come to the crux of the matter, illustrating why storing both pieces
of data in a single column is a bad idea.

Nobody has ever come up with a foolproof way of parsing first and last names
from unformatted strings. Sure, most names consist of two words separated by
a space. These are easy. But what about names consisting of 3 or more words.
How would you propose to handle that?

Go back to the beginning: store the two pieces of data in separate fields,
and force the input of the data into two separate textboxes.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[StressedMonkey]

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!

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

Cheers.

Miguel.

"StressedMonkey" <mi****@stressedmonkey.net-nospam> wrote in message
news:40***********************@news.dial.pipex.com ...

Hi,

I'm not too hot on my manipulation of strings. I have a database which
cintains names in the format (Upper case also) LASTNAME, FIRSTNAME

I want to search against that database, but the data on the form to produce the string will be input in the following format:

Firstname Lastname

So I will need to convert this string in the following ways:

1. Make all uppercase
2. Reverse the position of Firstname & Lastname
3. Add a comma and space between Lastname & Firstname

Basically, so that the string looks exactly as the names will in the
database.

Are there any Maestro's out there that can help me with some code to achieve this?

Thanks.

Miguel.

[StressedMonkey]

Actualy, ignore me, I've found a way of doing it in excel by copying the
data to a text file and imposting it as a csv.

Thanks for the help anyway.
"StressedMonkey" <mi****@stressedmonkey.net-nospam> wrote in message
news:40***********************@news.dial.pipex.com ...

Hi,

I'm not too hot on my manipulation of strings. I have a database which
cintains names in the format (Upper case also) LASTNAME, FIRSTNAME

I want to search against that database, but the data on the form to produce the string will be input in the following format:

Firstname Lastname

So I will need to convert this string in the following ways:

1. Make all uppercase
2. Reverse the position of Firstname & Lastname
3. Add a comma and space between Lastname & Firstname

Basically, so that the string looks exactly as the names will in the
database.

Are there any Maestro's out there that can help me with some code to achieve this?

Thanks.

Miguel.

[Bob Barrows [MVP]]

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.

[Stefan Berglund]

On Tue, 9 Mar 2004 08:40:18 -0500, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:
in <e$*************@TK2MSFTNGP12.phx.gbl>

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.

What is the problem with parsing names when presented as Last
comma First other than when the comma is omitted?

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

[Bob Barrows [MVP]]

Stefan Berglund wrote:

On Tue, 9 Mar 2004 08:40:18 -0500, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:
in <e$*************@TK2MSFTNGP12.phx.gbl>

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.

What is the problem with parsing names when presented as Last
comma First other than when the comma is omitted?

None - it's trivial to split on the comma, assuming the data is consistent.
It's going the other way that's the problem ... oh, I see. I misread the
above.

Yes, a macro could easily be written to split the data in the spreadsheet
into the appropriate columns. I'm not fluent with Excel VBA so the OP might
want to ask in an Excel newsgroup. Additionally, once the data is in a
database table, it would be very easy to write a query to split the names
into the appropriate columns. I do not consider it good database design to
store two pieces of data in the same column.

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.