5.3 Find Replace Tool: Lookup & Text Substitution

5.3 Find Replace Tool: Lookup & Text Substitution

Core Certification Focus: The Find Replace tool performs string searching, text substitution, and dictionary lookups without requiring exact relational join keys. Exam candidates must understand the two input anchors (F and R), the mandatory String data type requirement for search keys, the three search location options (Beginning of Field, Any Part of Field, Entire Field), the difference between Replace Found Text and Append Field(s) to Record, and why unmatched records in F are never dropped.

---

1. Tool Architecture & Anchor Mechanics

The Find Replace tool (located in the purple Join category) searches a specified text field in a primary dataset and matches it against a reference lookup table to either substitute text or append reference columns.

Tool Anchor Layout

• Input Anchors (2):
  • F (Find / Source Input): The primary incoming data stream containing records to be searched, updated, or enriched.
  • R (Replace / Reference Input): The lookup reference table containing the search patterns and replacement/append values.
• Output Anchors (1):
  • Output (Single Output): Outputs the complete dataset from F with substitutions applied or new columns appended.

+-----------------------------------------------------------------------------+
|                     FIND REPLACE ANCHOR ARCHITECTURE                        |
|                                                                             |
|   [ Primary Data Stream ]   ---> (F)                                        |
|                                      [ FIND REPLACE ] ---> (1 Output Anchor)|
|   [ Lookup Reference Table] ---> (R)     (Purple)                           |
+-----------------------------------------------------------------------------+

---

2. Configuration Window Properties

The Find Replace configuration pane is organized into three logical sections: Find Field Mapping, Find Options, and Replace/Append Actions:

+-----------------------------------------------------------------------------+
|                   FIND REPLACE CONFIGURATION PANE                           |
+-----------------------------------------------------------------------------+
| FIND:
|   Find within Field (F): [ CustomerAddress                                v ]
|   Find Value (R):        [ Abbreviation                                   v ]
| --------------------------------------------------------------------------- |
| FIND OPTIONS:
|   ( ) Beginning of Field   (o) Any Part of Field    ( ) Entire Field        |
|   [X] Case Insensitive Find                         [X] Match Whole Word    |
| --------------------------------------------------------------------------- |
| REPLACE / APPEND OPTIONS:
|   ( ) Replace Found Text                            (o) Append Field(s)
|       Replace With Field: [ FullName    v ]             [X] StateName       |
|       (o) Replace Multiple Found Items                  [X] RegionCode      |
|       ( ) Replace First Found Item                      [ ] TaxRate         |
+-----------------------------------------------------------------------------+

Section 1: Find Field Mapping

• Find within Field (F): Selects the string column in the primary stream (F) that contains text to search.
• Find Value (R): Selects the string column in the reference stream (R) containing search terms.
• Data Type Rule: Both fields MUST be String data types (String, V_String, WString, V_WString). Numeric or Date fields will not appear in these dropdowns.

Section 2: Find Options

• Beginning of Field: Matches only if the search string occurs starting at the very first character (index 0) of the F text field.
• Any Part of Field: Substring search; matches if the search string appears anywhere within the F text field.
• Entire Field: Exact full-field match; matches only if the entire F cell value equals the R search value from start to end.
• Case Insensitive Find (Checkbox): When checked, treats upper and lowercase letters as identical (e.g., "usa" matches "USA" and "Usa").
• Match Whole Word Only (Checkbox): Prevents partial word substitutions by enforcing word boundary checks (e.g., searching for "cat" will match "the cat sat" but will not match inside "catalog" or "scatter").

---

3. Action Modes: Replace Found Text vs. Append Field(s)

The developer must choose one of two distinct operational modes:

Mode A: Replace Found Text (Substitution Mode)

• Overwrites the matched text in the F stream using values from a designated replacement field in R.
• Radio Options:
  • Replace Multiple Found Items: Replaces every instance of the matched search string within each cell.
  • Replace First Found Item: Replaces only the first occurrence of the matched string in each cell, leaving subsequent occurrences unchanged.

Mode B: Append Field(s) to Record (Lookup Enrichment Mode)

• Preserves the original text in F and appends one or more reference columns selected from R to the right side of the record.
• Unmatched Record Handling: If a record in F does not match any search value in R, the record is still passed downstream, and the newly appended columns are populated with [Null].

---

4. Deep Comparison: Find Replace Tool vs. Join Tool

Understanding when to use Find Replace instead of Join is a cornerstone of the Core Certification exam:

---

5. Practical Use Cases in Data Workflows

USE CASE 1: STANDARDIZING ABBREVIATIONS IN TEXT (Replace Mode)
Primary Data (F):           Lookup Table (R):          Find Replace Output:
[Address]                   [Abbr]   [Expansion]       [Address]
"100 Main St, Apt 4B"  +    "St"     "Street"     -->  "100 Main Street, Apartment 4B"
"500 Elm Blvd"              "Apt"    "Apartment"       "500 Elm Boulevard"
                            "Blvd"   "Boulevard"

USE CASE 2: DICTIONARY CODE LOOKUP (Append Mode)
Primary Data (F):           Lookup Table (R):          Find Replace Output:
[CustID] [TypeCode]         [Code]   [Category]        [CustID] [TypeCode] [Category]
101      "RET"         +    "RET"    "Retail"     -->  101      "RET"      "Retail"
102      "CORP"             "CORP"   "Corporate"       102      "CORP"     "Corporate"
103      "GOV"                                         103      "GOV"      [Null]

---

6. High-Yield Exam Traps & Best Practices

[!WARNING] Exam Trap: Numeric Fields in Find Replace If you attempt to match numeric customer IDs or transaction amounts using Find Replace, the fields will not appear in the configuration dropdowns. You must convert numeric fields to V_String using an upstream Select tool before connecting to F or R.

[!IMPORTANT] Exam Trap: Unchecked "Match Whole Word Only" When replacing short abbreviations like state codes (e.g., "IN" for "Indiana"), failing to check "Match Whole Word Only" with "Any Part of Field" will disastrously corrupt words like "MAIN" into "MAIndiana" or "TRAINING" into "TRAIndianag".

[!NOTE] Exam Trap: Record Count Retention Unlike the Join tool (which drops unmatched rows unless unioned), the Find Replace tool never drops records from the F stream. The output record count of a Find Replace tool is guaranteed to equal the input record count of F.