Solved

Remove Middle Initial from Name Value of String Before Importing value to Database Table

Posted on 2008-06-18
4
537 Views
Last Modified: 2008-06-18
I am attempting to write code in a VBScript that will find and replace a pattern of  
space middle initial space
in a name value and replace it with a space only .
The value is coming from a text file that is being opened as an FSO and fields are defined through the use of the split function of each line in the file using Split(sLine, ",") The name field is one of the sections parsed in a line within this comma delimited text file.
I do not want to remove any additional names in the field. Some of the values in the name field have 2 last names that are not hypenated - I need to keep this the same.  Again, the pattern to be replaced should only be a single alpha character between two spaces.
For example, John S Doe to be John Doe.
John S Doe Smith will be changed to John Doe Smith.

I want the final format of the Name value to be updated before it gets inserted into a table.
0
Comment
Question by:MLG0001
  • 2
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
Badotz earned 125 total points
ID: 21815465
A regex might be the way to go:

dim re
dim str1
dim str2

Set re = new regexp ' create the RegExp object
re.Pattern = "\b [A-Za-z] \b" ' set the pattern (word_boundary + " " + letter + " " + word_boundary)
re.Global = True

str1 = "John S Doe Smith" ' for testing only; your data will come from elsewhere
str2 = re.Replace(str1, "") ' modified string
0
 

Author Comment

by:MLG0001
ID: 21817022
Thanks this works GREAT ! I did make one minor change - instead of the replace having a "" I changed it to " " to make a space between the first and last name values.
I do have one more question regarding this - I have come across a few names (yes the data i received is horridly inconsistent) that contain 3 middle initials. This code will remove 2 of the 3 just as it is set up to do - but is there to still use similar code and have it only remove the first middle initial value it finds instead of 2 of the 3 ? An example John T I S Doe updates to be John S Doe as the code is set above...If I want it to be John I S Doe ...is that possible?
0
 

Author Comment

by:MLG0001
ID: 21817201
Figured the answer out - Set Global  = False and it only returns the first match of the pattern.
Thanks again for your assistance!
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21817297
No worries - glad to help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now