Reduce
Cut out portions of text from the source field and save to the specified Result destination. The Start numbering is zero based: starting at the first letter of the text is a Start number of 0, and starting at the second letter of text is a Start number of 1.
- cut - Reduce to the text portion of given length beginning at the Start character.
- cut to end - Reduce to the text portion beginning at the Start character and continuing to the end of the text.
- end - Reduce to the last number of characters given in the Length. Using a Length of 5 would cut out and use the last 5 characters of the text.
- regular expression occurrence - Given an Occurrence number (zero based counting), and a regular expression, cut out the given occurrence of text that matches the regular expression. For example, an Occurrence number of 1 means the second occurrence. If the regular expression was e. (the letter e followed by any single character) and the starting text was Netherlands, the text would be reduced to er, which is the second occurrence in Netherlands of the letter e followed by any single character. Using an Occurrence of 0 (the first occurrence) of the same regular expression would result in et, which is the first instance of the letter e followed by any character. If the starting text was France, the result would be an empty text since there is no e followed by at least one character in France. Note that an empty text string is not the same as a NULL.
- start - Reduce to the first number of characters given in the Length. Using a Length of 5 would cut out and use the first 5 characters of the text.
Launch the template by choosing a text field and then double-clicking the Reduce template. When the template launches we can specify options.
Reduce : cut
Reduce to the text portion of given Length beginning at the Start character, and save to the specified Result destination.
Copies a group of characters from within a source field. For a Start number, n, (zero-based counting) and a given Length starting at and including the nth character position, copy the Length number of characters from the source field and save to the specified Result destination.
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.
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 Reduce template to launch it.
In the Reduce template we choose cut as the Reduce to option. For the Length we enter 4 and for the Start position we enter 2, meaning the third character in the text.
For the Result destination, we choose New Field, and we enter Text cut from 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 have chosen Same Field for the Result, to alter the source text field "in place," or if there were additional text fields in the table we could have chosen one of them as a Result destination.
Press Transform.
The template automatically creates a new nvarchar field called Text cut from Country and populates it with four characters of text cut from the Country field, beginning with the third character in the string.
With zero based counting a Start of 2 means to begin at the third character, inclusive.
Reduce : cut to end
Reduce to the text portion beginning at the Start character and continuing to the end of the text, and save to the specified Result destination.
Copies a group of characters from a starting position to the end of a source field. For a Start number, n, (zero-based counting), copy all characters from that start position, inclusive, to the end of the source field, and save to the specified Result destination.
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.
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 Reduce template to launch it.
In the Reduce template we choose cut to end as the Reduce to option. For the Start position we enter 2, meaning the third character in the text.
For the Result destination, we choose New Field, and we enter Text cut from 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 have chosen Same Field for the Result, to alter the source text field "in place," or if there were additional text fields in the table we could have chosen one of them as a Result destination.
Press Transform.
The template automatically creates a new nvarchar field called Text cut from Country and populates it with text cut from the Country field, from the third character in the string to the end of the string.
With zero based counting a Start of 2 means to begin at the third character, inclusive.
Reduce : end
Reduce to the last number of characters given in the Length, and save to the specified Result destination. Using a Length of 5 would cut out and use the last 5 characters of the text.
Copies a group of characters from the end of a source field. From the end of the source field copy the specified Length of characters and put into the target field.
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.
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 Reduce template to launch it.
In the Reduce template we choose end as the Reduce to option. For the Length we enter 4.
For the Result destination, we choose New Field, and we enter Text cut from 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 have chosen Same Field for the Result, to alter the source text field "in place," or if there were additional text fields in the table we could have chosen one of them as a Result destination.
Press Transform.
The template automatically creates a new nvarchar field called Text cut from Country and populates it with the last 4 characters of text cut from the Country field.
Reduce : regular expression occurrence
Given an Occurrence number (zero based counting), and a regular expression pattern in the Value box, cut out the given occurrence of text that matches the regular expression.
Use an Occurrence of 0 to extract the first instance of any match to the given regular expression pattern. Check the Ignore case box to treat upper case and lower case letters as the same.
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.
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 Reduce template to launch it.
In the Reduce template we choose regular expression occurrence as the Reduce to option. For the regular expression Value text, we enter the single . character, about the simplest regular expression we can write. The regular expression . (a single dot period character) matches any single character.
For Occurrence we enter 0, meaning the first occurrence..
For the Result destination, we choose New Field, and we enter Regexp extracted 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 Regexp extracted and populates it with text that is the first occurrence of the regular expression in the Country string.
The regular expression . (a single dot period character) matches any single character. The first occurrence of that regular expression is the first character in the Country string, which is the result saved into the new Regexp extracted field.
We can rerun the transform to experiment with other regular expression patterns.
We can run the transform again, this time entering er.* for the regular expression Value.
For the Result destination, we choose the new Regexp extracted field that was created in the table in the prior run.
Press Transform.
The regular expression er.* matches any occurrence of er followed by one or more characters. Country names such as France and Italy do not have any occurrences of er in their names, so the Regexp extracted field is empty for those records.
We try another regular expression:
We rerun the transform again, this time entering n.. for the regular expression Value. With the Ignore case box checked, and an Occurrence of 0, this matches the first occurrence of N or n followed by any two characters.
For the Result destination, we again use the Regexp extracted field that was created in the table in a prior run.
Press Transform.
The regular expression n.. with the Ignore case option checked matches occurrences of N or n followed by any two characters. That pattern occurs twice in Netherlands, with the first instance being Net and the second occurrence being nds. In France the first and only occurrence of the pattern is the nce letters at the end. In Germany and Switzerland the pattern does not occur since there is only one letter after the single n in Germany and Switzerland. In all cases where the pattern occurs, using 0 for Occurrence selects the first occurrence, so for Netherlands the first, Net occurrence is extracted.
Using an Occurrence other than 0, we can pick out whichever occurrence of a regular expression pattern is found in the source string.
We rerun the transform, entering a single . character for the regular expression Value. With an Occurrence of 1, this matches the second occurrence of any single character.
We again use the Regexp extracted field for the Result.
Press Transform.
The regular expression . (a single dot period character) matches any character. Counting starts from zero, so an Occurrence of 1 means the second instance of the pattern, that is, the second character in the Country string.
We rerun the transform using n.. for the regular expression Value. With the Ignore case box checked, and an Occurrence of 1, this matches the second occurrence of N or n followed by any two characters.
An Occurrence of 1 means the second occurrence (counting 0, 1, 2,...) of the pattern, which occurs twice only in Netherlands, the nds letters. There is no second occurrence in the other country names. In Germany and Switzerland there is not even a first occurrence since there is only one letter after the single n in Germany and Switzerland.
For a useful example, see the Get a Token From a String section of the Regular Expressions topic.
Reduce : start
Reduce to the first number of characters given in the Length, and save to the specified Result destination. Using a Length of 5 would cut out and use the first 5 characters of the text.
Copies a group of characters from the start of a source field. From the start of the source field copy the specified Length of characters and put into the target field.
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.
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 Reduce template to launch it.
In the Reduce template we choose start as the Reduce to option. For the Length we enter 4.
For the Result destination, we choose New Field, and we enter Text cut from 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 have chosen Same Field for the Result, to alter the source text field "in place," or if there were additional text fields in the table we could have chosen one of them as a Result destination.
Press Transform.
The template automatically creates a new nvarchar field called Text cut from Country and populates it with the first 4 characters of text cut from the Country field.