TRANSLATE()

All functions > STRING > TRANSLATE()

Returns a string with character-level replacements applied based on character-to-character mappings.

Signatures

Returns: String with character-level replacements applied

TRANSLATE(string: VARCHAR, from_chars: VARCHAR, to_chars: VARCHAR) → VARCHAR

Notes

• Character-by-character replacement (not whole substrings)
• Each character in from_chars maps to the character at the same index in to_chars
• If to_chars is shorter than from_chars, the extra source characters are removed from the result
• If any argument is NULL the result is NULL (use NULL(VARCHAR); bare NULL fails inference)

Examples

SELECT
    -- Replace digits with letters
    f1 := TRANSLATE('12345', '123', 'ABC'),
    -- Case change for specific chars
    f2 := TRANSLATE('Hello World', 'ol', 'OL'),
    -- Multiple replacements
    f3 := TRANSLATE('AABBCC', 'ABC', 'XYZ'),
    -- Replace dashes with dots
    f4 := TRANSLATE('123-45-6789', '-', '.'),
    -- No matching characters
    f5 := TRANSLATE('test', 'abcd', 'ABCD'),
    -- NULL yields NULL
    f6 := TRANSLATE(NULL(VARCHAR), 'a', 'b')
;