Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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 from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Delete numbers and special characters

timgerr (IS/IT--Management) (OP)
27 Dec 07 14:38
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!!!

guitarzan (Programmer)
27 Dec 07 16:40
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.
dm4ever (TechnicalUser)
27 Dec 07 17:34
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

timgerr (IS/IT--Management) (OP)
7 Jan 08 14:29
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!!!

EBGreen (Programmer)
7 Jan 08 14:35
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

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!

Back To Forum

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