Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2008-06-18
4
541 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

790 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