Right() and Left() functions are only useful for manipulating constant strings or a constant number of characters. The SubStr() function is a more powerful function. Use the SubStr() function to extract strings where the length and position of the specified strings are not consistent.
Creating Formulas with Character Functions
Objectives
After completing this lesson, you will be able to:
- Create formulas using character functions
Character String Functions
Functions in Formulas and Variables
You use the functions of character strings to create a formula. You create a formula by typing it in the Formula field or in the Formula Editor. Either method calculates the data and displays the resulting values in the column that you have highlighted in the block.
You can also drag and drop objects in the Formula field and complete the formula by typing in the Formula field.
The table shows a comparison of the use of variables and formulas.
Variables vs Formulas Comparison
| Variables | Formulas |
|---|---|
| Can be used in different blocks or reports in the document | Only for use in a single cell |
| Can be identified as an object | N/A |
If in doubt, always create a variable instead of a formula because there are certain things that can be done with variables that cannot be done with formulas.
Character Extraction Using the Right() Function
The Year object in the eFashion.unx universe is formatted to return the year as a four-digit value (for example, 2017). However, to display the year in a two-digit format (for example, 17), use the Right() character string function to display the last two characters.
The Right() function displays a fixed number of characters, starting from the right of the character string, and removes the others.
The character string function Left() works in the same way, except that the function displays the specified number of characters starting from the left.
Syntax
Returns the rightmost characters of a string.
Characters Extracted Using the SubStr() Function
The Store name object in the eFashion.unx universe returns the name of each store in the company eFashion. However, each store name is prefixed by e-Fashion.
Suppose that you want to remove the e-Fashion portion of the character string. Formatting the report to remove the e-Fashion prefix is a challenge because the length of each store name is different, with or without the e-Fashion portion. Neither the Right() or Left() functions works here.
You can remove the e-Fashion prefix by using the SubStr() function.
The SubStr() function extracts a specified character string from within a string.
Syntax
Returns part of a string.
String Replacement Using the Replace() Function
The Replace() function will modify the character string displayed.
Syntax
Replaces part of a string with another string.
string Replace(replace_in ; replaced_string ; replace_with)
Character Position Using the Pos() Function
You are working on a report that contains the Store and Address columns.
In the Address column, you want to display only the street names for each of the eFashion stores. The Address object from the universe returns both the building number and the street name. The number of spaces before the street name begins is inconsistent. Therefore, you must create a substring to return only the portion of the address that is needed.
Notice that in each address, there is a space after the building number. If you can, create a formula that recognizes the position of the space for each entry. Use that position as a starting point for the SubStr() function.
Create a variable Space that contains the syntax:
Create a variable Address that contains the syntax:
