Working with Substrings

As I mentioned, string manipulation is often valuable when dealing with user input. For example, suppose you have a script that will work with a server, and you want the user to enter the server name in an input box. You might start with something like this.


Function GetServer()

 Dim sServer

 sServer = InputBox("Work with what server?")

 GetServer = sServer

End Function

NOTE

There doesn't seem much point in making this a special function at present, but bear with me. By the way, don't bother typing in these scriptlets yet-I'll be building on this example throughout the chapter.

The problem is that the user could type nearly anything. If this is a script that only you will be using, you can probably be sloppy and leave it as-is, knowing that you'll always type the right thing. However, if a junior administrator or technician will use the script, you should program some intelligence into it.

As an example, suppose the administrator typed a UNC-style name, such as \\Server1. If your script is expecting a simple name like Server1, the extra characters could cause problems. You can build your function to manipulate the string.


Function GetServer()

 Dim sServer

 sServer = InputBox("Work with what server?")



 'trim backslashes

 Do While Left(sServer,1) = "\"

  sServer = Right(sServer, Len(sServer) - 1)

 Loop



 'return result

 GetServer = sServer

End Function

In this new example, a Do…Loop construct is used to examine the leftmost character of sServer. As long as the leftmost character is a backslash, the loop will set sServer equal to sServer's rightmost characters. This is done with the Right() function, which accepts sServer as its input string, and then accepts the current length of sServer (via the Len() function), minus one, as the number of characters to pull. The result is that all but the leftmost character-which is known to be a backslash at this point-is saved. The loop repeats until the leftmost character is no longer a backslash.

I haven't covered Do…Loop yet, but if you want to read up on it quickly, skip ahead to "Loops" in Chapter 10.

Suppose your company's server naming convention always starts with a few letters, then a hyphen, and then finishes up with numbers. Perhaps the letters indicate which office the server is located in, and you want to pull that information out so that a user account (or something else) can be created in the appropriate Active Directory organizational unit (OU). No problem.


Function GetOffice(sServerName)



 'find the hyphen

 Dim iHyphen

 iHyphen = InStr(1, sServerName, "-")



 'get just the part before the hyphen

 Dim sOffice

 sOffice = Left(sServerName, iHyphen - 1)



 'return result

 GetOffice = sOffice

End Function

In this function, I've used the InStr() function to locate the first occurrence of a hyphen within sServerName. Suppose the server name in this case is PHL-77432; the hyphen is at location 4, so variable iHyphen will now contain a 4.

Next, I used Left() to grab the leftmost characters before the hyphen. In this case, I only want the leftmost three characters, so the Left() function is asked to return iHyphen - 1, which in this example evaluates to the leftmost three characters.

Notice the 1, the first input parameter to InStr(). That tells InStr() to start searching at the first character of sServerName. Suppose your server names look something like WIN-7745-PHL and you want to get the office code (PHL). In that case, you need to find the first hyphen, and then start looking after it for the second hyphen.


Function GetOffice(sServerName)



 'find the first hyphen

 Dim iHyphen1

 iHyphen1 = InStr(1, sServerName, "-")



 'find the second hyphen

 Dim iHyphen2

 iHyphen2 = InStr(iHyphen1, sServerName, "-")



 'get just the part after the 2nd hyphen

 Dim sOffice

 sOffice = Right(sServerName, Len(sServerName) - iHyphen2)



 'return result

 GetOffice = sOffice

End Function

First, this script locates the first hyphen by having InStr() start at the beginning of sServerName. Then, the script locates the second hyphen by having InStr() start at the location after the first hyphen. Finally, the script uses the Right() function to get everything after the second hyphen. This is done by taking the length of sServerName (which is 12 in this example) and subtracting the character location of the second hyphen (which is 9), giving us the rightmost three characters we want.

You could do this same task with a bit less code by using InStrRev().


Function GetOffice(sServerName)



'find the second hyphen

 Dim iHyphen2

 iHyphen2 = InStrRev(sServerName, "-")



 'get just the part after the 2nd hyphen

 Dim sOffice

 sOffice = Right(sServerName, iHyphen2 - 1)



 'return result

 GetOffice = sOffice

End Function

In this example, InStrRev() would return 4, because the second hyphen is four characters from the end of WIN-7745-PHL. The Right() function is told to subtract one from that value, giving us the rightmost three characters we want.

TIP

Playing with substrings and the associated calculations can be a bit of fun, like working out a puzzle. I find it's often easier to think of an example string and write it down on paper in large letters. I then number each letter with its character position. Doing so makes it easier to work out the math of the Left(), Right(), InStr(), and InStrRev() functions.

But wait, there's one more substring function! Mid() makes it possible to pull substrings from the middle of other strings. For example, suppose you need to pull the second three characters from a string such as "492NYCFILES." You could use Left() to get the leftmost three characters, and then use Right() to get the rightmost three characters from that. Or, you could just use Mid("492NYCFILES",4,3) to start at the fourth character and pull three characters. If all of your server names were formatted that way, you might rewrite the GetOffice() function as follows:


Function GetOffice(sServerName)

 Dim sOffice

 sOffice = Mid(sServerName, 4, 3)

 GetOffice = sOffice

End Function

Table of Contents