Table of Contents
FILTER BY SEARCH
Category: Transform / Filters
Description
This action keeps or removes rows where values in a specified column contain a particular substring or match a regular expression.
Use cases
- Create subsets of a dataset based on parts of values in a specific column (literal strings or regular expressions).
- Clean a dataset by removing records that may have "junk" data in a specific column.
Action settings
| Setting | Description |
|---|---|
| Column | Select the column containing the values to be used for filtering. |
| Search type | Select what part of the values should match the search string. See the table below. |
| Search string* | Enter the string or regular expression to match in the selected column's values. If "Regular expression" is selected as the Search type, this setting changes to "Regular expression". |
| Regular expression | This setting appears when "Regular expression" is selected as the Search type. Enter the regular expression to be evaluated against all values in the selected column. See regular expression for more information. |
| Case sensitive | When checked, the case will not be considered when matching the search string. |
| Filter mode | Select whether rows with matching values will be kept in the dataset, or removed. Options: Keep only matching values or Remove matching values. |
* Setting can be specified using a parameter.
Remarks
In cases where no values match the search string:
- If Keep only matching values was selected, the result will be an empty dataset.
- If Remove matching rows was selected, the result will be the full dataset (no rows removed).
Examples
Example #1
Find all rivers that start with 'Y'.
Before (source table)
| River | Length (km) | Continent |
|---|---|---|
| Nile | 6650 | Africa |
| Amazon | 6400 | South America |
| Yangtze | 6300 | Asia |
| Mississippi | 6275 | North America |
| Yenisei | 5539 | Asia |
| Yellow River | 5464 | Asia |
| Ob | 5410 | Asia |
| Paraná | 4880 | South America |
After (result table)
| River | Length (km) | Continent |
|---|---|---|
| Yangtze | 6300 | Asia |
| Yenisei | 5539 | Asia |
| Yellow River | 5464 | Asia |
Action parameters
Column: River
Search type: Starts with
Search string: "Y"
Example #2
Find the phone numbers using the "(###) ###-####' format.
Before (source table)
| phone_nums |
|---|
| 111-222-3333 |
| (555) 555-5555 |
| +1 777-888-9999 |
| 444 555 6666 |
| (123) 456-7890 |
After (result table)
| phone_nums |
|---|
| (555) 555-5555 |
| (123) 456-7890 |
Action parameters
Column: phone_nums
Search type: Regular expression
Regular expression: "^\(\d{3}\)\s\d{3}-\d{4}$"
Keep only matching values
Community examples
- Finding all projects with particular action (Project; Module: Main; Group: Tab 1; Table: Table 2; Action position: 3)
- Download multiple files from HTTPS and copy to folder, preserving file names (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 3)
- How to search a text file (e.g. task log) and see result in Server Pages (Project; Module: Main; Group: Tab 1; Table: Table 1;
Action position: 2)