×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Replace Name Formatting

Replace Name Formatting

Replace Name Formatting

(OP)
Hi!

I have names that I want to change in format like this...

LASTNAME, FIRSTNAME MI.

Below the names in my database having two different formats.

LASTNAME;FIRSTNAME;MI.;
FIRSTNAME MI LASTNAME


THANKS! smile

RE: Replace Name Formatting

There are lots of ways of doing this. Here's one suggestion for starters:

CODE -->

REPLACE ALL NameField WITH ;
  IIF(OCCURS(";", NameField) > 0, ;
    GETWORDNUM(NameField, 1, ";") + ", " + GETWORDNUM(NameField, 2, ";") + " " + GETWORDNUM(NameField, 3, ";"), ;
    GETWORDNUM(NameField, 3) + ", " + GETWORDNUM(NameField, 1) + " " + GETWORDNUM(NameField, 2)) 

I haven't tested that, so can't guarantee that is is 100 percent correct, but it should give you a good start.

Edit: Minor syntax errors in above code, now corrected.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Replace Name Formatting

Quote (Mike Lewis)

Edit: Minor syntax errors in above code, now corrected.

Funnily enough as I was reading your post I was thinking that there's another couple of VFP COMMANDS that I'm not too familiar with, so I thought I take a look at OCCURS. As you have advised me in the past to read the VFP Help File, I did just that.

Looking at your code and the help file together I thought something doesn't add up here, the NAMEFIELD and the SemiColon are the wrong way round. Can't be, I'll return to later!

Although I don't post a lot on here, I do read your posts and take note. Keep up the good work.

Regards,

David.

Recreational user of VFP.

RE: Replace Name Formatting

Thanks, David. And you were right about the field name and the semi-colon being the wrong way round. You spotted it before I had a chance to correct it.

Getting cSearchExpression and cExpressionSearched the wrong way round is one of my most frequent mistakes. I do it in AT() and ATLINE() and similar functions, as well as OCCURS(). You would think I would have learned by now.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Replace Name Formatting

GETWORDCOUNT will be helpful to cover the cases with and without middle initial differently and check LEN() of the MI to be 1 or 2 only, ie check for a plausible result. Better make it an ICASE instead of IIF.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Replace Name Formatting

I'm wondering if the OP accidentally or deliberately left out the period from after the middle initial in his second example and whether he/she wants one or not.

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: Replace Name Formatting

Obviously have a backup, and, what I also do in such cases, as first step before replacing save just the to be changed data for easy record by record recovery:

CODE

SELECT Id, Namefield as OrigName FROM yourtable INTO TABLE originalnames 

Bye, Olaf

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Replace Name Formatting

Quote:

Obviously have a backup

Or buffer the table so that you can revert if something goes wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Replace Name Formatting

If you have a list you can manually check, okay, but if there are 1000s or even 10000s of names, a buffer, once committed, is also not revertable.
You should have backups of data anyway, so you could assume that to be already checked from your todo list, but an explicit copy is helpful to redo the cases you didn't think of in pass 1. Also, if you're external to the company you might not get at the backup or it becomes a bureaucratic act to get an admin to restore it.

Anyway, all just food for thought and recommendations.

The case almost messing your data would be the surely quite common "FIRSTNAME LASTNAME", btw. The non-existent 3rd word is empty and you'd replace with ",FIRSTNAME LASTNAME" with a leading comma. It would be data loss, when GETWORDNUM would return NULL for such cases.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Replace Name Formatting

Doesn't look like anyone else has said this, but in general, it's a bad idea to store full names in a single field. If you simple have three fields, one for each name, you can then assemble them however you want for display.

Tamar

RE: Replace Name Formatting

Tamar's right, of course. As so often happens in this forum, we attempt to solve the problem exactly as it is presented to us, without looking behind it to see the wider question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Replace Name Formatting

Mike, it's not wrong, and you also just gave "a good start". With all givens, it works.

Some people lately stated that's actually the core of a forum, even Dan Freeman once recommended I could focus on the core question. But I say that's not where the name forum comes from. It's not a task repository or programming on commission. It's also a discussion basis, an exchange, and a knowledge base, for the asker and future searchers, too.
It's all OK, as it's interactive.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close