> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for SUBSTRING function

# SUBSTRING, SUBSTR

## SUBSTRING, SUBSTR

Returns a substring starting at the character indicated by the `<start>` index and including the number of characters defined by `<length>`.
Character indexing starts at index 1.

## Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SUBSTRING(<expression> [FROM <start>] [FOR <length>])
SUBSTRING(<expression>, <start> [, <length>])
SUBSTR(<expression>, <start> [, <length>])
```

## Parameters

| Parameter      | Description                                                                                                                                                                                                  | Supported input types |
| :------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<expression>` | The input string.                                                                                                                                                                                            | `TEXT`,`BYTEA`        |
| `<start>`      | Optional. The starting position for the substring. 1 is the first character. If value \< 1 then it start from negative position (all negative positions treated as empty).                                   | `BIGINT`              |
| `<length>`     | Optional. The number of characters to be returned by the `SUBSTRING` function. Must be positive or 0. If not specified, `count` by default returns all of the string not specified by the `start` parameter. | `BIGINT`              |

At least one of the optional arguments `<start>` and `<length>` must be given.

Negative `<start>` values begin the substring before the input `<expression>`. Positions before the input `<expression>` are considered empty.

## Return Type

`TEXT` or `BYTEA`. The type of `<expression>`

##### Example

In the example below, the string is offset by 1 and so the `SUBSTRING` command begins at the first letter, "h". The `<length>` of 5 indicates the resulting string should be only five characters long.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
	SUBSTRING('hello world' FROM 1 FOR 5);
```

**Returns**: `hello`

In this next example `<length>` is not provided. This means all characters are included after the `<start>` index, which is 7.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
	SUBSTRING('hello world' FROM 7);
```

**Returns**: `world`

For negative `<start>` values, empty positions before the input string are selected. Thus, without `<length>` negative values will select the entire input `<string>`.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
	SUBSTRING('hello world' FROM -2);
```

**Returns**: `hello world`

With a negative `<start>` value and `<length>`, the empty positions before the string are counted towards the substring length. In this example, beginning the substring at `-2` for `8` characters includes 3 empty positions and 5 positions with characters from the input `<expression>`.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
	SUBSTRING('hello world' FROM -2 FOR 8);
```

**Returns**: `hello`