Shape / columns · All operations

Replace values

Replace or map values, find and replace, value mapper.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Map values through a lookup

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, region, coalesce(MAP {'north':'N','south':'S','east':'E','west':'W'}[region], '?') AS region_code FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE region_code = '?') = 1 AND count(*) FILTER (WHERE region = 'north' AND region_code = 'N') = 3 FROM result)) THEN 'as expected' ELSE error('the lookup did not code north as N and the missing region as ?') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the lookup did not code north as N and the missing region as ?”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Replace errors, infinities and invalid values

Replace error values, infinities, values that do not fit the column type, or chosen values with null.

Map given values to new ones

Replace whole cell values that match given values with new values, leaving the rest or using a default.

Group rare or unseen values

Replace infrequent values, or values not seen in training, with one placeholder.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Dynamic Replaceread 2026-09-27

    Replace values across fields by conditions and replacements held in a second table

Apache Hop (3)
  • Replace in Stringread 2026-09-27

    Search and replace text in string fields, by literal or regular expression

  • Token replacementread 2026-09-27

    Replace named tokens in a template string or file with values from fields

  • Value Mapperread 2026-09-27

    Map source values of a field to target values from a lookup list

Apache NiFi (1)
  • ReplaceTextWithMappingread 2026-09-27

    Replace content matched by a regular expression with values from a mapping file

Apache SeaTunnel (1)
  • Replaceread 2026-09-27

    Replace substrings of a field that match a literal or a regex

Duckle (1)
EasyMorph (5)
  • Clean upread 2026-09-27

    Blank out given values, or values of a given type, in one or more columns

  • Groupread 2026-09-27

    Replace a chosen set of values with one constant or calculated value, as a group label or flag

  • Replaceread 2026-09-27

    Replace given values in a column with new values

  • Replace with lookupread 2026-09-27

    Replace substrings in one or many columns using a lookup table of replacements

  • Table-wide replaceread 2026-09-27

    Apply one replacement or removal of values across all or selected columns

Ibis (1)
  • StringReplaceread 2026-09-27

    Replace a literal substring in a string with another string

KNIME Analytics Platform (2)
  • String Replacerread 2026-09-27

    Replace strings in a column that match a wildcard or regex pattern

  • String Replacer (Dictionary)read 2026-09-27

    Replace strings using patterns and replacements looked up in a dictionary table

OpenRefine (1)
  • Replace…read 2026-09-27

    Find and replace text in a column, with case, whole-word and regular-expression options

Pentaho Data Integration (2)
  • Replace in Stringread 2026-09-27

    Replace every occurrence of a word in a string with another

  • Value Mapperread 2026-09-27

    Map the values of a field from one value to another

Polars (2)
  • Expr.replaceread 2026-09-27

    Swap given values for others of the same type, leaving the rest untouched

  • Expr.replace_strictread 2026-09-27

    Map every value to a new one, erroring or using a default when a value has no mapping

Power Query M (3)
  • Replacer functionsread 2026-09-27

    2 functions used by replace operations: Replacer.ReplaceText, Replacer.ReplaceValue

  • Table.ReplaceErrorValuesread 2026-09-27

    Replace error values in named columns with a given value per column

  • Table.ReplaceValueread 2026-09-27

    Replace one value with another in named columns, as whole value or as text within it

RapidMiner Studio (5)
  • Handle Unknown Valuesread 2026-09-27

    Remember the nominal values seen in training and replace unseen ones when applied to new data

  • Mapread 2026-09-27

    Map given values of selected columns to new values, with optional default and regex

  • Mergeread 2026-09-27

    Merge two values of a nominal column into one

  • Replace Infinite Valuesread 2026-09-27

    Replace positive or negative infinity with zero, missing, the column maximum or a given value

  • Replace Rare Valuesread 2026-09-27

    Replace infrequent nominal values with one common placeholder value

Tableau Prep (2)
  • Manual Selectionread 2026-09-27

    Pick by hand the values to map to a standard value; new values can be added

  • Replace with Nullread 2026-09-27

    Replace selected values of a field with null

Talend (2)
  • tReplaceread 2026-09-27

    Search and replace values in chosen columns

  • tReplaceListread 2026-09-27

    Search and replace values in chosen columns using pairs from a lookup flow

Trifacta (7)

Background

Data cleansing detects incomplete, incorrect or inaccurate parts of the data and then replaces, modifies or deletes them.[1] In Polars, Expr.replace replaces the given values by different values of the same data type.[2]

When to use it

In Power Query, replacing goodbye with world as a whole value changes a cell holding goodbye and leaves a cell holding goodbyes as it was.[3] In EasyMorph, the Replace action replaces only whole values, not partial matches.[4] Decide first whether each rule is about whole cells or about text inside them. (Meridian's view)

In Polars, Expr.replace leaves the values it was not given unchanged.[2] In Polars, Expr.replace_strict raises an error if any non-null value was not replaced and no default was given.[5] In pandas, Series.map with a dictionary converts values that are not keys of the dictionary to NaN, unless the dictionary defines a default.[6] In Apache Hop, the Value Mapper transform can keep an unmatched source value, write a default in its place, or return null.[7] Say what should happen to a value that no rule names, whether it is kept, given a default or stops the run, rather than leaving it to a tool's default. (Meridian's view) Where new codes can arrive in later loads, a mapping that fails on an unseen value is the safer choice, because it surfaces the new code instead of hiding it. (Meridian's view)

In DuckDB, any comparison with a NULL value returns NULL, including NULL = NULL.[8] In Apache Hop's Value Mapper, a NULL input with no mapping defined for NULLs returns NULL, not the default target.[7] Treat missing values as a case of their own in every replacement rule. (Meridian's view)

The method

In PostgreSQL, the simple form of CASE computes an expression and compares it with each WHEN value until one is equal, returning the ELSE result, or null, if none is.[9] In PostgreSQL, a CASE expression returns the result that follows the first WHEN condition that is true and does not process the rest.[9] In DuckDB, a CASE expression with no ELSE clause returns NULL when none of its conditions match.[10]

In DuckDB, the SWITCH expression is shorthand for CASE that takes an expression, a MAP of values to results and an optional default value.[10] In DuckDB, looking up a key that a MAP does not contain returns NULL rather than raising an error.[11] In PostgreSQL, COALESCE returns the first of its arguments that is not null, and is often used to substitute a default value for null values.[9] In an associative array, a lookup finds the value bound to a key, and when there is none some lookup functions raise an exception while others return a default value.[12] Strict and lenient replacement are those two answers to a missing key, applied to every value of a column. (Meridian's view)

In Polars, Expr.replace keeps the column's original data type when the new values are of a different type, while Expr.replace_strict can change it.[2]

Compared with the alternatives

In pandas, DataFrame.replace finds the values to change by what they are, whereas updating with .loc or .iloc needs the location of each value.[13] In PostgreSQL, NULLIF returns null when its two arguments are equal, so it can undo a COALESCE that put a placeholder such as (none) in place of null.[9]

Sources

  1. [1]Data cleansing - Wikipediasecondary source
  2. [2]polars.Expr.replace — Polars documentationdocumentation
  3. [3]Table.ReplaceValue - PowerQuery M | Microsoft Learndocumentation
  4. [4]REPLACE [EasyMorph Help]documentation
  5. [5]polars.Expr.replace_strict — Polars documentationdocumentation
  6. [6]pandas.Series.map — pandas 3.1.1 documentationdocumentation
  7. [7]Value Mapper | Apache Hopdocumentation
  8. [8]NULL Values – DuckDBdocumentation
  9. [9]PostgreSQL: Documentation: 18: 9.18. Conditional Expressionsdocumentation
  10. [10]CASE Expression – DuckDBdocumentation
  11. [11]Map Type – DuckDBdocumentation
  12. [12]Associative array - Wikipediasecondary source
  13. [13]pandas.DataFrame.replace — pandas 3.1.1 documentationdocumentation

Used in

3 Protocols use this operation: