Replace Name Formatting 1

[TheLazyPig]

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!

 

[Mike Lewis]

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

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

 

[David Higgs]

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.

 

[Mike Lewis]

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

 

[Olaf Doschke]

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

 

[GriffMG]

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 [Smile]ing

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

 

[Olaf Doschke]

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:

SELECT Id, Namefield as OrigName FROM yourtable INTO TABLE originalnames

Bye, Olaf

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

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

 

[Olaf Doschke]

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

 

[TamarGranor]

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

 

[Mike Lewis]

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

 

[Olaf Doschke]

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