translate function

Applies to: Databricks SQL Databricks Runtime

Returns an expr where all characters in from have been replaced with those in to.

Syntax

translate(expr, from, to)

Arguments

• expr: A STRING expression.
• from: A STRING expression consisting of a set of characters to be replaced.
• to: A STRING expression consisting of a matching set of characters to replace from.

Returns

A STRING`.

The function replaces all occurrences of any character in from with the corresponding character in to.

If to has a shorter length than from unmatched characters are removed.

Examples

> SELECT translate('AaBbCc', 'abc', '123');
 A1B2C3

> SELECT translate('AaBbCc', 'abc', '1');
 A1BC

> SELECT translate('AaBbCc', 'abc', '');
 ABC

> SELECT translate('AaBbCc' COLLATE UTF8_LCASE, 'abc', '123');
 112233

Related functions

• replace function
• overlay function
• regexp_replace function