Substitute all (subsall)
Replace all the matches of a regular expression in a given string and replaces it by an indicated pattern in template format. If no occurrence is found, it returns the original expression or an optional specified value. Use the Substitute (subs) operation to replace only the first occurrence.
How does it work in the search window?
Select Create column in the search window toolbar, then select the Substitute all operation. You need to specify at least three arguments:
|String to scan (mandatory)||string|
|Regular expression (mandatory)||string|
The data type of the values in the new column is string.
demo.ecommerce.data table, we want to replace all the : occurrences in every string of our timestamp column by a -. To do it, we will create a new column using the Substitute all operation.
- String to scan- timestamp column
- Regular expression - Click the pencil icon and enter :
- Template - Click the pencil icon and enter -
Note that Devo automatically changes the strings manually entered in the Regular expression and Template arguments to the required regexp and template data types. If you want to use a column on these arguments, it must be a regexp/template type column. You can use the Regular expression, regexp (re) and Template (template) operations to transform a string column to the required data type.
Click Create column and you will see the following result:
How does it work in LINQ?
Use the operator
as... and add the operation syntax to create the new column. These are the valid formats for the Substitute all operation:
subsall(string, re(string), template(string))
subsall(string, re(string), template(string), fail_value_string)
subsall(string, regexp, template)
subsall(string, regexp, template, fail_value_string)
Note that when you enter a string value as a regular expression and template using LINQ, you have to transform them to regexp and template format using the Regular expression, regexp (re) and Template (template) operations, as you can see in the examples. This is not needed if you perform this operation directly from the search window interface, as said above.
You can copy the following LINQ script and try the above example on the
from demo.ecommerce.data select subsall(timestamp, re(":"), template("-")) as substitute_timestamp_all