substr 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 substring function.

Syntax 

substr(expr, pos [, len] )

substr(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

The result matches the type of expr.

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 substr('Spark SQL', 5);
 k SQL
> SELECT substr('Spark SQL', -3);
 SQL
> SELECT substr('Spark SQL', 5, 1);
 k
> SELECT substr('Spark SQL' FROM 5);
 k SQL
> SELECT substr('Spark SQL' FROM -3);
 SQL
> SELECT substr('Spark SQL' FROM 5 FOR 1);
 k
> SELECT substr('Spark SQL' FROM -10 FOR 5);
 Spar