vbscript - Replace all spaces

4
votes

I have 6400+ records which I am looping through. For each of these: I check that the address is valid by testing it against something similar to what the Post Office uses (find address). I need to double check that the postcode I have pulled back matches.

The only problem is that the postcode may have been inputted in a number of different formats for example:

OP6 6YH
OP66YH
OP6  6YH.

If Replace(strPostcode," ","") = Replace(xmlAddress.selectSingleNode("//postcode").text," ","") Then

I want to remove all spaces from the string. If I do the Replace above, it removes the space for the first example but leave one for the third.

I know that I can remove these using a loop statement, but believe this will make the script run really slow as it will have to loop through 6400+ records to remove the spaces.

Is there another way?

3
vbscriptreplace
I do not follow. Specifically, your third example looks identical to the first one - how can the Replace result be different? You are making a lot of assumptions - have you even tried it first? Where do your "records" come from - a database query? Talk is cheap. Show me the code (Linus Torvalds). - PointedEars
I had put two spaces within the first one, but Stackoverflow must have removed the second space. Yes I have tired the Replace function and it ONLY removed the first space (if there are two spaces together). A CSV file and a XML file. - ClareBear

3 Answers

5
votes

I didn't realise you had to add -1 to remove all spaces

Replace(strPostcode," ","",1,-1)
4
votes

Personally I've just done a loop like this:

Dim sLast
Do
    sLast = strPostcode
    strPostcode = Replace(strPostcode, " ", "")
    If sLast = strPostcode Then Exit Do
Loop

However you may want to use a regular expression replace instead:

Dim re : Set re = New RegExp
re.Global = True
re.Pattern = " +"  ' Match one or more spaces
WScript.Echo re.Replace("OP6 6YH.", "")
WScript.Echo re.Replace("OP6  6YH.", "")
WScript.Echo re.Replace("O  P   6       6    Y     H.", "")
Set re = Nothing

The output of the latter is:

D:\Development>cscript replace.vbs
OP66YH.
OP66YH.
OP66YH.
D:\Development>
2
votes

This is the syntax Replace(expression, find, replacewith[, start[, count[, compare]]])

it will default to -1 for count and 1 for start. May be some dll is corrupt changing the defaults of Replace function.