Delete numbers and special characters

[timgerr]

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.

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]

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

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]

Simple RegEx

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]

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]

Just change the pattern

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

[red]"... 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]