Text Functions
So far, all the functions you've seen have worked with numeric data. However, there's still a lot you can do with text. Overall, there are three ways you can manipulate text:
- Join text. You can do things like combining several fields together into one field. This technique doesn't require a functioninstead, you can use the & operator.
- Extract part of a text value. You may want just the first word in a title or the first 100 characters in a description.
- Change the capitalization. You may want to show lowercase text in capitals, and vice versa.
Table-4 shows the most common functions people use with text.
Table-4. Functions for Text| Function | Description | Example | Result |
|---|---|---|---|
| UCase( ) | Capitalizes text | UCase("Hi There") | HI THERE |
| LCase( ) | Puts text in lowercase | LCase("Hi There") | hi there |
| Left( ) | Takes the number of characters you indicate from the left side | Left("Hi There", 2) | Hi |
| Right( ) | Takes the number of characters you indicate from the right side | Right("Hi There", 5) | There |
| Mid( ) | Takes a portion of the string starting at the position you indicate, and with the length you indicate | Mid("Hi There", 4, 2) | Th |
| Trim( ) | Removes blank spaces from either side (or use LTrim( ) and RTrim( ) to trim spaces off just the left or right side) | Trim("Hi There") | Hi There |
| Len( ) | Counts the number of characters in a text value | Len("Hi There") | 8 |
Using these functions, you can create a calculated field that shows a portion of a long text value, or changes its capitalization. However, how you can use these functions in a filter expression may not be as obvious. You could create a filter condition that matches part of a text field, instead of the whole thing. Here's an example of a filter condition that selects records that start with Choco:
Left([ProductName], 5) = "Choco"
The Len( ) function's a bit of an oddity. It examines a text value and returns numeric information (in this case, the number of characters in the value, including all spaces, letters, numbers, and special characters). The Len( ) function isn't too useful in a simple calculated expression, because you'll rarely be interested in the number of letters in a text value. However, it does let you write some interesting filter conditions, including this one that grabs records with a Description of less than 15 characters (which probably could use some extra information):
Len(Description) < 15