overlay function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Replaces input with replace that starts at pos and is of length len.

Syntax 

overlay(input, replace, pos[, len])

overlay(input PLACING replace FROM pos [FOR len])

Arguments

  • input: A STRING or BINARY expression.

  • replace: An expression of the same type as input.

  • pos: An INTEGER expression.

  • len: An optional INTEGER expression.

Returns

The result type matches the type of input.

If pos is negative the position is counted starting from the back. len must be greater or equal to 0. len specifies the length of the snippet within input to be replaced. The default for len is the length of replace.

Examples 

> SELECT overlay('Spark SQL', 'ANSI ', 7, 0);
 Spark ANSI SQL
> SELECT overlay('Spark SQL' PLACING '_' FROM 6);
 Spark_SQL
> SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
 Spark CORE
> SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
 Spark ANSI SQL
> SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
 Structured SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') FROM 6);
[53 70 61 72 6B 5F 53 51 4C]