mask function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 and above

Returns a masked version of the input str.

In Databricks SQL and Databricks Runtime 13.3 and above this function supports named parameter invocation.

Syntax

mask(str [, upperChar [, lowerChar [, digitChar [, otherChar ] ] ] ] )

Arguments

  • str: A STRING expression.

  • upperChar: A single character STRING literal used to substitute upper case characters. The default is 'X'. If upperChar is NULL, upper case characters remain unmasked.

  • lowerChar: A single character STRING literal used to substitute lower case characters. The default is 'x'. If lowerChar is NULL, lower case characters remain unmasked.

  • digitChar: A single character STRING literal used to substitute digits. The default is 'n'. If digitChar is NULL, digits remain unmasked.

  • otherChar: A single character STRING literal used to substitute any other character. The default is NULL, which leaves these characters unmasked.

Returns

A STRING.

Examples

> SELECT mask('AaBb123-&^ % 서울 Ä');
  XxXxnnn-&^ % 서울 X

> SELECT mask('AaBb123-&^ % 서울 Ä', 'Z', 'z', '9', 'X');
  ZzZz999XXXXXXXXXZ

> SELECT mask('AaBb123-&^ % 서울 Ä', lowerchar => 'z', otherchar => 'X');
  AzBz123XXXXXXXXXÄ

> SELECT mask('AaBb123-&^ % 서울 Ä', otherchar => '?');
  AaBb123?????????Ä

> SELECT mask('AaBb123-&^ % 서울 Ä', NULL, NULL, NULL, NULL);
  AaBb123-&^ % 서울 Ä