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!

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

Jobs

Delete numbers and special characters

Delete numbers and special characters

(OP)
I have a database that I need to clean up and I have to get rid of some characters and other stuff in it.  I have to do it in vbscript so I need a little help.  I can query and return the data fine.  

Here is the problem, I have some entries that are supposed to be last names but because of laziness people have entered some gibberish here is an example.

CODE

NewMan001
Goldberg(#44345)
is there a way to tell that a character is not alpha and how do I remove them?

Thanks,
timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
-Need more cow bell!!!

RE: Delete numbers and special characters

One very low-tech (and slow) solution would be passing each string into this function:

CODE

Dim s
s = "Goldberg(#44345)"
s = FixName(s)
wscript.echo s


Function FixName(s)
   Dim ch
   Dim sAcceptableChars
   sAcceptableChars = "abcdefghijklmnopqrstuvwxyz"

   For x = 1 to Len(s)
      ch = mid(s, x, 1)
      If InStr(sAcceptableChars, LCase(ch)) > 0 Then
         FixName = FixName & ch
      End If
   Next

End Function

Just add any other characters you want to sAcceptableChars (like a space, or apostrophe maybe).

HOWEVER, this would be much more efficiently solved with Regular Expressions, I'm just not good enough with them to provide an example.

RE: Delete numbers and special characters

Simple RegEx

CODE

Option Explicit

Dim arrTemp : arrTemp = Array("NewMan001", "Goldberg(#44345)")
Dim RegEx : Set RegEx = New RegExp
RegEx.Pattern = "[^A-Z]+"
RegEx.IgnoreCase = True
Dim strTemp
For Each strTemp In arrTemp
    WScript.Echo RegEx.Replace(strTemp, "")
Next

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

RE: Delete numbers and special characters

(OP)
OK, I have been using this but I have one problem I have some apostrophes ' withing the name, like o'brien, o'tool.  I can I keep the apostrophes in?

Thanks,
timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
-Need more cow bell!!!

RE: Delete numbers and special characters

Just change the pattern

RegEx.Pattern = "[^A-Z']+"

"... isn't sanity really just a one trick pony anyway?!  I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick

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!

Resources

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