Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-18
4
Medium Priority
?
553 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
[X]
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
  • 2
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
Badotz earned 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Simple Linear Regression

715 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