translate function

Applies to: check marked yes Databricks SQL check marked yes 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