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

# SUBSTRING

> Extracts part of a string or binary value.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Scalar function</span></div>

**Aliases:** [`substr`](/query/sql/functions/string/substr), [`mid`](/query/sql/functions/string/mid)

NULL arguments return NULL. Position 0 starts at the beginning. Nonpositive length returns an empty value. Strings count characters; binary values count bytes.

## Syntax

### To end (scalar)

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
substring(value, position)
```

### Scalar

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
substring(value, position, length)
```

## Arguments

### To end

| Name | Type | Required | Description |
| - | - | - | - |
| value | STRING or BINARY | Yes | Input string or binary value. |
| position | INTEGER or BIGINT | Yes | Position starts at 1. Zero starts at the beginning; a negative position counts from the end. |

### Scalar

| Name | Type | Required | Description |
| - | - | - | - |
| value | STRING or BINARY | Yes | Input string or binary value. |
| position | INTEGER or BIGINT | Yes | Position starts at 1. Zero starts at the beginning; a negative position counts from the end. |
| length | INTEGER or BIGINT | Yes | Maximum number of characters or bytes. A nonpositive length returns an empty value. |

## Returns

* Type: STRING or BINARY
* NULL behavior: NULL arguments return NULL. Position 0 starts at the beginning. Nonpositive length returns an empty value. Strings count characters; binary values count bytes.

## Properties

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports window usage | No |
| Exact result | Yes |

## Units

* Input unit: Characters for STRING; bytes for BINARY.
* Output unit: Characters for STRING; bytes for BINARY.
* Omitting length reads through the end. Positions past the available input produce an empty value.

## Usage notes

* Position and length are ordinary arguments. Neither is a pre-call configuration parameter or required to be a constant.
* The two-argument form has no length argument; it reads through the end instead of substituting a numeric default.

## Examples

### Substring with a length limit

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT substring('abcdef', 2, 3) AS result;
```

| result |
| - |
| bcd |

### Omitted length

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT substring('abcdef', 3) AS result;
```

| result |
| - |
| cdef |

### Negative and zero positions

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT substring('abcdef', -2, 2) AS negative_position, substring('abcdef', 0, 2) AS zero_position;
```

| negative\_position | zero\_position |
| - | - |
| ef | ab |

### Empty results

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT substring('abcdef', 2, 0) AS zero_length, substring('abcdef', 2, -1) AS negative_length, substring('abcdef', 20, 2) AS past_end;
```

| zero\_length | negative\_length | past\_end |
| - | - | - |
| '' | '' | '' |

### NULL input

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT substring(CAST(NULL AS VARCHAR), 2, 3) AS result;
```

| result |
| - |
| NULL |

### Binary positions count bytes

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT hex(substring(X'616263646566', 2, 3)) AS result;
```

| result |
| - |
| 626364 |

### Non-integer position is rejected

* The explicit DOUBLE cast demonstrates the integer-argument restriction.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT substring('abcdef', CAST(1.5 AS DOUBLE), 2) AS result;
```

Expected error: Unsupported position or length type

### MID rejects a non-integer position

* MID requires integer position and length arguments. This call is rejected.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT mid('abcdef', CAST(1.5 AS DOUBLE), 2) AS result;
```

Expected error: Cannot apply 'MID'

## Limitations

* Use integer positions and lengths. Other numeric types and STRING values are outside these calling forms.

## Related functions

* [left](/query/sql/functions/string/left)
* [right](/query/sql/functions/string/right)
* [length](/query/sql/functions/string/length)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.