Edit

SUBSTRING (NoSQL query)

  • Article

APPLIES TO: NoSQL

Returns part of a string expression starting at the specified position and of the specified length, or to the end of the string.

Syntax

SUBSTRING(<string_expr>, <numeric_expr_1>, <numeric_expr_2>)

Arguments

Description
string_expr A string expression.
numeric_expr_1 A numeric expression to denote the start character.
numeric_expr_2 A numeric expression to denote the maximum number of characters of string_expr to be returned.

Return types

Returns a string expression.

Examples

The following example returns substrings with various lengths and starting positions.

SELECT VALUE {
    substringPrefix: SUBSTRING("AdventureWorks", 0, 9),
    substringSuffix: SUBSTRING("AdventureWorks", 9, 5),
    substringTotalLength: SUBSTRING("AdventureWorks", 0, LENGTH("AdventureWorks")),
    substringEmptyString: SUBSTRING("AdventureWorks", 0, -1)
}

[
  {
    "substringPrefix": "Adventure",
    "substringSuffix": "Works",
    "substringTotalLength": "AdventureWorks",
    "substringEmptyString": ""
  }
]

Remarks

  • This function benefits from a range index if the starting position is 0.
  • numeric_expr_1 positions are zero-based, therefore a value of 0 starts from the first character of string_expr.
  • A value of 0 or less for numeric_expr_2 results in empty string.

Related content