Trim
Remove characters from the start, the end, or from both the start and end of a string, removing either whitespace characters, any character that occurs in a custom list of characters or both whitespace and custom list characters. By default the template trims whitespace characters from the start and the end of the source text.
- end - Trim any specified characters from the end of the source text.
- start - Trim any specified characters from the start of the source text.
- start and end - Trim any specified characters from both the start and the end of the source text.
The Characters box allows specifying what characters will be trimmed.
- custom - Pop open a Value box, and any characters in that box will be trimmed.
- whitespace - Trim any whitespace characters. A whitespace character is a character like a space character or a tab, but also non-printing characters such as line feed, form feed, and carriage return.
- whitespace and custom - Trim both whitespace characters as well as any character that appears in the Value box.
The Value box that appears for custom and whitespace and custom choices allows specifying a field in the table, a value that is a list of characters, or an expression. For example, using the value choice, to trim both upper case E and lower case e from a text, enter Ee into the Value box. When used with start and end, that will trim Europe to urop. To trim all lower case and upper case vowels, enter aeiouyAEIOUY into the Value box. When used with start and end, that will trim Europe to rop. Since both upper case E and lower case u are found in the Value box list, both characters will be trimmed from the start of the text.
Launch the template by choosing a text field and then double-clicking the Trim template. When the template launches we can specify options.
Trim : end
Trim any specified characters from the end of the source text and save to specified Result destination. The most common use of Trim is to eliminate extra whitespace characters, so there is a default choice of whitespace for the characters to trim.
Whitespace characters cannot be seen at the end of strings, so we will use an example that trims other characters.
We use a starting table giving the names of countries where we have edited the table to add some extra c and e characters at the beginning and at the end of strings. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table, in the Transform pane, the Country field is chosen by default. We double-click the Trim template to launch it.
In the Trim template we choose end as the Trim option. For the Characters text, we choose custom from the pull down menu and then we enter the characters ezqc into the Value box.
We only care about the c and the e characters, but we have used four characters to illustrate how the order in which we list characters does not matter, and to show how characters we put in the Characters list do not matter if they do not appear at the beginning or the end of the text.
For the Result destination, we choose New Field, and we enter Trimmed country as the name of the new field. We choose the default nvarchar text data type offered for the new field.
Press Transform.
The template automatically creates a new nvarchar field called Trimmed country and populates it with text from the Country field that has had any characters removed from the end of the text that are found in the Characters list.
The trimming process starts at the end and removes any characters that are found in the Characters list, continuing until it encounters a character not found in the Characters list. It does not matter in what order characters appear in the Characters list. Once the trim process stops, because a character not in the Characters list was encountered, it does not matter that deeper within the string any character in the Characters list may appear.
For example, although the e character appears in the Characters list, the e characters inside Netherlands are not removed and the z character that appears in the Characters list in our example is not removed from within Switzerland.
We can see how Trim removes repeated occurrences of characters from the Characters list, trimming eFranceee to eFran. We can also see how it trims single occurrences of listed characters at the end of Italyc to create Italy, or trimming France to Fran. No changes are made to the beginning of strings.
Trim : start
Trim any specified characters from the start of the source text and save to specified Result destination. The most common use of Trim is to eliminate extra whitespace characters, so there is a default choice of whitespace for the characters to trim.
Whitespace characters cannot be seen at the ends of strings, but whitespace characters can be seen at the start of strings by the indentation they cause. For a more interesting example, we will use a table with strings that mix whitespace and other characters at the beginning of strings.
We use a starting table giving the names of countries where we have edited the table to add some whitespace characters and also some extra c and e characters at the beginning and at the end of strings. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table, in the Transform pane, the Country field is chosen by default. We double-click the Trim template to launch it.
In the Trim template we choose start as the Trim option. For the Characters text, we choose whitespace and custom from the pull down menu and then we enter the characters ezqc into the Value box.
We only care about whitespace characters and the c and the e characters, but we have used four characters to illustrate how the order in which we list characters does not matter, and to show how characters we put in the Characters list do not matter if they do not appear at the beginning or the end of the text.
For the Result destination, we choose New Field, and we enter Trimmed country as the name of the new field. We choose the default nvarchar text data type offered for the new field.
Press Transform.
The template automatically creates a new nvarchar field called Trimmed country and populates it with text from the Country field that has had any characters removed from the start of the text that are found in the Characters list, as well as any whitespace characters at the start of the text.
The trimming process starts at the beginning of the text and removes any characters that are found in the Characters list as well as any whitespace characters, continuing until it encounters a non-whitespace character not found in the Characters list. It does not matter in what order characters appear in the Characters list. Once the trim process stops, because a non-whitespace character not in the Characters list was encountered, it does not matter that deeper within the string any character in the Characters list may appear.
For example, although the e character appears in the Characters list, the e characters inside Netherlands are not removed and the z character that appears in the Characters list in our example is not removed from within Switzerland.
We can see how Trim removes repeated occurrences of characters from the Characters list, trimming c c c c France to France. We can also see how it trims single occurrences of listed characters at the beginning of cNetherlandsc to create Netherlandsc. No changes are made to the ends of strings.
Trim : start and end
Trim any specified characters from both the start and the end of the source text and save to specified Result destination. The most common use of Trim is to eliminate extra whitespace characters, so there is a default choice of whitespace for the characters to trim.
We start with a table that has an nvarchar text field called Country, which contains records with the names of countries. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
Space characters at the end of a string are invisible, but we can see extra spaces at the beginning of a string. The table above has had some extra space characters inserted at the beginning of strings. Using the default Characters setting of whitespace will get rid of all extra spaces both at the beginning and the end of the source string.
With the focus on the table, in the Transform pane, the Country field is chosen by default (since it is the only field - we could have chosen it if there were other fields). We double-click the Trim template to launch it.
In the Trim template we choose start and end as the Trim option. For the Characters text, we leave the default whitespace choice. If we prefer, in the pull down menu for the Characters choice we could choose custom or whitespace and custom if we wanted to trim custom characters other than the usual whitespace characters.
For the Result destination, we choose New Field, and we enter Trimmed country as the name of the new field. We choose the default nvarchar text data type offered for the new field. If we wanted, we could choose Same Field and trim the Country field "in place," or if there were any other text fields in the table we could choose those.
Press Transform.
The template automatically creates a new nvarchar field called Trimmed country and populates it with text from the Country field that has had any whitespace characters at the beginning or at the end of the text removed.
We can trim characters other than whitespace characters, using the custom choice for Characters.
Consider a variation of our starting table where we have edited the table to add some extra c and e characters at the beginning and at the end of strings.
With the focus on the table, in the Transform pane, the Country field is chosen by default. We double-click the Trim template to launch it.
In the Trim template we choose start and end as the Trim option. For the Characters text, we choose custom from the pull down menu and then we enter the characters ezqc into the Value box.
We only care about the c and the e characters, but we have used four characters to illustrate how the order in which we list characters does not matter, and to show how characters we put in the Characters list do not matter if they do not appear at the beginning or the end of the text.
For the Result destination, we choose New Field, and we enter Trimmed country as the name of the new field. We choose the default nvarchar text data type offered for the new field.
Press Transform.
The template automatically creates a new nvarchar field called Trimmed country and populates it with text from the Country field that has had any characters removed from the beginning and the end of the text that are found in the Characters list.
The trimming process starts at the end or beginning and removes any characters that are found in the Characters list, continuing until it encounters a character not found in the Characters list. It does not matter in what order characters appear in the Characters list. Once the trim process stops, because a character not in the Characters list was encountered, it does not matter that deeper within the string any character in the Characters list may appear.
For example, although the e character appears in the Characters list, the e characters inside Netherlands are not removed and the z character that appears in the Characters list in our example is not removed from within Switzerland.
We can see how Trim removes repeated occurrences of characters from the Characters list, trimming ccccFrance to Fran. We can also see how it trims single occurrences of listed characters at the end of Italyc to create Italy.