This chapter is from the book
This chapter is from the book
This chapter is from the book
Manipulating Text Strings
You use string functions to manipulate groups of text data. The following are some examples of where you might use string functions:
- Checking to see whether a string contained another string
- Parsing out a portion of a string
- Replacing parts of a string with another value
The following string functions help you do all these tasks and more.
The Asc Function
Every individual character can be represented by a number value. These values are listed in the American Standard Code for Information Interchange (ASCII). To return the ASCII value of a character use the following syntax, where string is an expression that results in a Text data type. It returns an integer value between 0 and 255.
ASC(string)
With any text string you must use apostrophes or quotation marks to define and delineate the text string; otherwise, Asc returns an error. However, if the string is a numeric, the delimiters can be eliminated. For example, the following two functions both return the value 51:
Asc("3")
Asc(3)
The Chr Function
The Chr function is the reverse of the Asc function. Whereas Asc returns the numerical value from character, Chr returns the character from a number. The following is the syntax for this function. where charactercode is an integer value between 0 and 255:
Chr(charactercode)
As you saw previously, the character 3 is represented by the number 51. So the following functions returns a 3:
Chr(51)
The Case Functions
There is actually no case function. There are two functions, LCase and UCase, that can be used to change the case of a text string. They use the following syntax, where string is an expression that returns a string value. Both functions return the string in either lowercase or uppercase, respectively.
LCase(string) UCase(string)
The Len Function
The Len function is used to determine the number of characters in a text string. This function uses the following syntax, where string is an expression that results in a Text data type. The function returns a long integer except where the string is Null, in which case it returns a Null value.
Len(string)
The Left, Right, and Mid Functions
Among the most used functions, these three return a portion of a string depending on the function and the arguments provided. All three result in a Variant Long subtype but support a $ version, which forces a String data type.
The Left and Right functions use a similar syntax:
Left(string, length) Right(string, length)
Here, string is an expression that results in a Text data type to be parsed and length is an expression that results in an Integer data type that specifies the number of characters from either the left or right end of the string to return.
The Mid function can parse a text string from any part of the string. It uses the following syntax, where string is a required argument that represents an expression resulting in a Text data type and start is a required argument that specifies where to start parsing the string:
Mid(string, start[, length])
An optional argument, length, specifies how many characters from the start point to parse. If length is omitted or is greater than the number of characters to the end of the string, all characters from start are returned. Figure 4.5 shows the three functions parsing various parts of the same string.
)
Figure 4.5 The Left, Right, and Mid functions parsing the same text.
The Replace Function
The Replace function is used to replace one or more characters within a string with a different character or characters. This function takes the following syntax, where string is an expression representing the text string to be searched, stringtoreplace is an expression representing the string to be searched for, and replacementstring represents the string you want in place of stringtoreplace
Replace(string, stringtoreplace, replacementstring[, start[, count[, compare]]])
In addition, there are three optional arguments: start, which specifies where to start searching within the string; count, which specifies the number of replacements to process; and compare, which is a constant indicating the method used to compare stringtoreplace with string. Table 4.5 lists the constants that can be used.
Table 4.5. Comparison Constants
|
Constant |
Value |
Description |
|
vbUseCompareOption |
-1 |
Performs a comparison using the setting of the Option Compare statement. |
|
vbBinaryCompare |
0 |
Performs a binary comparison. |
|
vbTextCompare |
1 |
Performs a textual comparison. |
|
vbDatabaseCompare |
2 |
Microsoft Access only. Performs a comparison based on information in your database. |
The Split Function
The Split function takes a delimited string and populates an array with the parts. The following is the syntax for the Split function, where string is a delimited string of values:
Split(string[, delimiter[, count[, compare]]])
This is the only required argument. The first optional argument is delimiter, which specifies the delimiting character separating the values. If you omit delimiter a space is assumed to be the delimiter. The second optional argument is count, which limits the number of values parsed. For example, there might be five values separated by commas in the string, but a count argument of 3 parses only the first three values. The final optional argument is compare. See Table 4.5 for the comparison constants.
The Trim Functions
Three functions can be used to trim leading or trailing spaces from a string. All three use the same syntax, where string is an expression that results in a Text data type:
Trim(string) LTrim(string) RTrim(string)
The Trim function removes both leading and trailing spaces, LTrim removes the leading spaces, and Rtrim removes the trailing spaces. All three functions return a Variant String subtype and support the $ format to force a Text data type.