substring function

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

Returns the substring of expr that starts at pos and is of length len. This function is a synonym for substr function.

Syntax 

substring(expr, pos [, len])

substring(expr FROM pos [FOR len] ] )

Arguments

  • expr: An BINARY or STRING expression.

  • pos: An integral numeric expression specifying the starting position.

  • len: An optional integral numeric expression.

Returns

A STRING.

pos is 1 based. If pos is negative the start is determined by counting characters (or bytes for BINARY) from the end.

If len is less than 1 the result is empty.

If len is omitted the function returns on characters or bytes starting with pos.

Examples 

> SELECT substring('Spark SQL', 5);
 k SQL
> SELECT substring('Spark SQL', -3);
 SQL
> SELECT substring('Spark SQL', 5, 1);
 k
> SELECT substring('Spark SQL' FROM 5);
 k SQL
> SELECT substring('Spark SQL' FROM -3);
 SQL
> SELECT substring('Spark SQL' FROM 5 FOR 1);
 k
> SELECT substring('Spark SQL' FROM -10 FOR 5);
 Spar