TRANSLATE: Map Characters Simultaneously Instead of Cascading Replacements

TRANSLATE maps each input character once, which matters when replacement characters also appear in the search list. Nested REPLACE calls can process earlier replacements again. I compare both results before choosing a cleanup expression.

A small mapping makes the distinction visible. Map a to b, b to c, and c to d. The input abc should become bcd when each original character is mapped once.

Read the two expressions side by side

The query uses four inputs with an explicit Unicode text type. It includes the same characters in different positions, surrounding characters, and NULL. No table, temporary object or session setting is required.

TRANSLATE receives two matching three-character lists. Position one maps a to b, position two maps b to c, and position three maps c to d. I don’t treat these lists as a three-character search token. They describe individual character pairs.

The nested expression deliberately applies three REPLACE calls in sequence. Its innermost call replaces a with b. The next call can replace both original b characters and newly created b characters.

WITH Inputs AS
(
    SELECT CaseId, InputText
    FROM (VALUES
        (1, CAST(N'abc' AS nvarchar(20))),
        (2, N'cab'),
        (3, N'xabcx'),
        (4, NULL)
    ) AS v(CaseId, InputText)
)
SELECT CaseId, InputText,
       TRANSLATE(InputText, N'abc', N'bcd') AS MappedOnce,
       REPLACE(REPLACE(REPLACE(InputText, N'a', N'b'), N'b', N'c'), N'c', N'd') AS NestedReplacement
FROM Inputs
ORDER BY CaseId;

Follow the expected results

For abc, the expected TRANSLATE result is bcd. Each output position follows its original input character. The replacement b is not fed through the same mapping again.

The nested result is ddd. After the first call, the text is bbc; after the second, it is ccc. The final call then changes all three c characters to d.

The input cab produces dbc with TRANSLATE. Reordering the original characters reorders their individual outputs. The nested chain still collapses the three positions to ddd.

The surrounding x characters stay unchanged in both expressions. This makes xabcx become xbcdx or xdddx respectively. A character outside the mapping does not require an additional preservation rule.

Decide whether a cascade is intentional

I use a simultaneous mapping when each original character has one replacement. Normalizing several punctuation characters to a common separator can fit that requirement. The mapping itself should be explicit and easy to inspect.

A sequence of replacements can be useful when later steps are supposed to consume earlier results. That is a different transformation contract. Changing nested REPLACE to TRANSLATE without checking the contract can change valid output.

TRANSLATE is available in SQL Server 2017 and later. Its two mapping lists must have the same length. The data types do not have to match. Different lengths produce an error, so an empty replacement list is not a deletion shortcut.

REPLACE can target a multi-character pattern. TRANSLATE works through character correspondence instead. A requirement to replace an entire word therefore needs a different expression from this example.

Include missing input in the contract

The final input is NULL, and both displayed expressions have NULL results. TRANSLATE returns NULL if any argument is NULL. Substituting an empty string would erase the distinction between missing text and present empty text.

The example keeps Unicode literals throughout the mapping. In a real pipeline, check the input type and expected character set before standardizing values. A correct mapping cannot recover characters already lost during an earlier conversion.

This comparison establishes the meaning of the two expressions, not their relative speed. Test the chosen expression against representative inputs before applying it widely. Include characters that overlap the mapping and characters that should remain untouched.

Keep the original value available when reviewing a transformation. The paired result columns make differences easy to explain without guessing which replacement ran first. Once the required behavior is clear, retain only the expression that implements that contract.

A tiny test with overlapping characters saves a lot of guessing later.

TRANSLATE is not a faster REPLACE, it is a one-pass character map.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.