Transform - Text: Replace

The Replace template appears in the template list when a variable length text field, of type nvarchar or varchar, has been picked in the Transform pane.  The template provides basic search and replace functionality using simple search and replace or using regular expressions.

 

Replace

Replace portions of text with other text.   The text to be replaced can be specified either as plain text or as a regular expression.

 

  • regular expression - Search for the regular expression pattern given in Search for and replace all occurrences of that regular expression pattern with the text given in Replace with.
  • regular expression occurrence - Search for the regular expression pattern given in Search for and replace only the occurrence (zero based counting) given in Occurrence of that regular expression pattern with the text given in Replace with.  For example, search for e. and replace with X with an Occurrence of 0 replaces the first instance of the letter e followed by any letter with X, to transform Netherlands into NXherlands, and to transform Germany into GXmany, but not to alter France, where the only letter e is not followed by any letters.
  • text - Search for the text given in Search for and replace all occurrences with the text given in Replace with.
  • text occurrence - Search for the text given in Search for and replace only the occurrence (zero based counting) given in Occurrence of that text with the text given in Replace with.  For example, search for e and replace with X with an Occurrence of 1 replaces the second occurrence of e with X, to transform Netherlands into NethXrlands.

 

Launch the template by choosing a text field and then double-clicking the Replace template.  When the template launches we can specify options.

 

 

Replace : regular expression

When we really want to replace something, this is the way.   Search for the regular expression pattern given in Search for and replace all occurrences of that regular expression pattern with the text given in Replace with, saving the result into the specified Result destination.  If no instances of the regular expression pattern are found, copy the source field unmodified into the specified Result destination.   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 Replace template to launch it.

 

 

In the Replace template we choose regular expression as the Replace option.   For the regular expression Search for text, we enter land, and for the Replace with text we enter water.

 

For the Result destination, we choose New Field, and we enter Replaced regexp 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 Replaced regexp and populates it with text that searches for land in the Country field and replaces it with water.   Strings in the Country field that do not have land in them are copied unchanged into the Replaced regexp field.

 

Regular expressions are often thought of as complex assemblies of special characters, but simple regular expressions can involve just single, ordinary characters.  

 

 

We can run the transform again, this time entering the single character e for the regular expression Search for pattern and the single character * for the Replace with string.   Note that the Replace with string is just an asterisk * character, and not a wildcard or regular expression pattern.

 

For the Result destination, we choose the new Replaced regexp field that was created in the table in the prior run.  

 

Press Transform.

 

 

A regular expression pattern of e matches any single occurrence of the letter e.  This example shows how the Search for string is a regular expression pattern but the Replace with text is just a string literal.  Whatever we put into the Replace with box is taken as a literal string.  

 

For very intricate replacements, we could choose Expression from the pull down menu for the Replace with box, and then put an SQL expression that evaluates to a string into the Replace with box.  For example, we might use a regular expression to find dates in a field and then replace them with a computed date.  See the example below for the  Replace : text template.

 

Replace : regular expression occurrence

Search for the regular expression pattern given in Search for and replace only the occurrence (zero based counting, so that an Occurrence of 0 means the first occurrence) given in Occurrence of that regular expression pattern with the text given in Replace with, saving the result into the specified Result destination.    If the specified occurrence of the regular expression pattern is not found, copy the source field unmodified into the specified Result destination.   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 Replace template to launch it.

 

 

In the Replace template we choose regular expression occurrence as the Replace option.   

 

We enter the single character e for the regular expression Search for text.  We enter the single character * for the Replace with string.   Note that the Replace with string is just an asterisk * character, and not a wildcard or regular expression pattern.

 

We enter 1 for the Occurrence. With zero-based counting, this means the second occurrence of the Search for pattern.

 

For the Result destination, we choose New Field, and we enter Replaced regexp 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 Replaced regexp and populates it with text that searches for the second occurrence of the single character e in the Country field and replaces it with an asterisk * character.   Strings in the Country field that do not have a second occurrence of the e character are copied unchanged into the Replaced regexp field.

 

Another example:

 

 

We run the transform again, this time entering the pattern  n. (the n character followed by a . period character) for the regular expression Search for pattern and the characters xy for the Replace with string.    As before, we use an Occurrence of 1.  We check the Ignore case box.

 

For the Result destination, we choose the new Replaced regexp field that was created in the table in the prior run.  

 

Press Transform.

 

 

A regular expression pattern of n. with Ignore case checked matches any occurrence of N or n followed by one letter.  An Occurrence of 1 tells us (zero based counting) to look at the second occurrence of that pattern, which only occurs twice in Netherlands.  That second occurrence is replaced with xy and all the other countries are just copied.  The Ignore case setting is essential to get the result below, because otherwise there is no second lower case n in Netherlands.  

 

Replace : text

A simple "replace all" operation.  Search for the text given in Search for and replace all occurrences with the text given in Replace with, saving the result into the specified Result destination.   If no occurrences of the Search for text are found, copy the source field unmodified into the 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 Replace template to launch it.

 

 

In the Replace template we choose text as the Replace option.   For the Search for text, we enter Germany, and for the Replace with text we enter Deutschland.

 

For the Result destination, we choose New Field, and we enter Replaced text 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 Replaced text and populates it with text that searches for
Germany
in the Country field and replaces it with Deutschland.   Strings in the Country field that do not have Germany in them are copied unchanged into the Replaced regexp field.

 

If we would like a more complex replacement, we first choose Expression from the pulldown menu for the Replace with box, and then we can put an SQL expression that evaluates to a string into the Replace with box.

 

 

For example, we can run the transform again, using a Search for text of lands and a Replace with expression of

 

CAST(DateTimeYear(#12/25/2021#) AS NVARCHAR)

 

For the Result destination, we choose the new Replaced text field that was created in the table in the prior run.  

 

Press Transform.

 

 

This example shows how an SQL expression can be used within a parameter box for the transform template.   

 

Instead of providing a literal text string we choose Expression in the pulldown menu and then use the DateTimeYear(<date>) SQL function to extract the year from the specified date.  

 

In SQL, literal dates are specified within grid # characters.   The result must be a text string so we do a CAST to turn the numeric result of the function into an nvarchar string.

 

Instead of using a literal date hard-wired into the expression, we can write an expression that uses other fields.  Suppose we have a Date field that gives a datetime value for each record:

 

 

In that case, we could use the Date field in an expression:

 

 

We can run the transform again, using a Search for text of land (in the singular this time) and a Replace with expression of

 

CAST(DateTimeDay([Date]) AS NVARCHAR)

 

For the Result destination, we again choose the Replaced text field that was created earlier.

 

Press Transform.

 

 

All instances of land are replaced with the day of the month value from the Date field.  

 

When we write an SQL expression into an expressions box  we do not have to use literals only.   We can refer to other fields within square [ ] brackets.  This example shows how we use a Date field in the table as the source of the date from which we extract the day of month using Manifold's DateTimeDay SQL function.

 

It is of course an artificial example to replace characters within the name of a country with parts of a date.   But in a table where we have dates or where there are comments or other fields where we would like to automatically replace some value with a text string created by a simple SQL expression, that is very easy to do as demonstrated above.

 

The Replace : text operation is basically analogous to the Replace : regular expression operation except that instead of searching for a regular expression pattern it searches for the given text.

 

Is the ability to use expressions like CAST(DateTimeDay([Date]) AS NVARCHAR) in a combo box seriously cool?  You bet.

 

Tech Tip:  To search for or replace using special characters, first choose Expression in the Search for or Replace with boxes, and then use Unicode escapes within single quote ' characters, such as the tab character or line feed characters   '\u0009' matches Unicode tab characters,  '\u000a' matches the Unicode line feed character and '\u000d' matches the Unicode newline character.

Different Languages

Manifold text transforms automatically support different languages by using collations.

 

 

The Collation choice shows the collation to be used for that language, such as Greek collation shown above.  The default is neutral, nocase, using the default language set by Windows.   We can also use the collation to set options such as considering case or accents.

 

Clicking the collation picker button allows us to change the collation, to choose from a list of Favorites, and to add and edit Favorite collations.  

 

See the Collations topic for details and examples.

 

Replace : text occurrence

Search for the text given in Search for and replace only the occurrence (zero based counting) given in Occurrence of that text with the text given in Replace with, saving the result into the specified Result destination.    If the specified occurrence of the Search for text is not found, copy the source field unmodified into 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 Replace template to launch it.

 

 

In the Replace template we choose text occurrence as the Replace option.   For the Search for text, we enter an, and for the Replace with text we enter oon.   For the Occurrence, we enter 0, meaning the first occurrence using zero-based counting.

 

For the Result destination, we choose New Field, and we enter Replaced text 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 Replaced text and populates it with text that searches for the first occurrence of an in the Country field and replaces it with oon.   Strings in the Country field that do not have at least one occurrence of an in them are copied unchanged into the Replaced text field.

 

The Replace : text occurrence operation is basically analogous to the Replace : regular expression occurrence operation except that instead of searching for a regular expression pattern it searches for the given text.

Different Languages

Manifold text transforms automatically support different languages by using collations.

 

 

The Collation choice shows the collation to be used for that language, such as French collation shown above.  The default is neutral, nocase, using the default language set by Windows.   We can also use the collation to set options such as considering case or accents.

 

Clicking the collation picker button allows us to change the collation, to choose from a list of Favorites, and to add and edit Favorite collations.  

 

See the Collations topic for details and examples.

 

 

See Also

Transform Pane

 

Transform Reference

 

Transform - Expression

 

Transform - Text

 

Transform - Text: Case

 

Transform - Text: Compose

 

Transform - Text: Concatenate

 

Transform - Text: Copy

 

Transform - Text: Encrypt

 

Transform - Text: Expression

 

Transform - Text: Pad

 

Transform - Text: Reduce

 

Transform - Text: Reverse

 

Transform - Text: Trim